The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Results 1 to 4 of 4

Thread: Need help in VB programming...

  1. #1

    Need help in VB programming...

    Hi all!

    I'm stuck on a script I'm trying to write for an Excel spreadsheet and it's in VB (or VBA... ?). This is the section of the script giving me hell:
    Code:
            For Each i In Content
                ' Get row of i
                iAddress = i.Address
                Row = CInt(FindWord(iAddress, 3, "$"))
                If (Trigger = False) Then
                    FirstRow = Row
                    Trigger = True
                End If
                ' If row contains the current word being searched...
                If InStr(UCase(i.Value), UCase(FindWord(sSearch, w, ","))) > 0 Then
                    ' Insert Row at the top, move this row, delete Old empty row
                    Debug.Print ("----Match Found, Moving Up to FirstRow")
                    Rows(CStr(FirstRow) & ":" & CStr(FirstRow)).Select
                    Selection.Insert Shift:=xlDown
                    Rows(CStr(Row + 1) & ":" & CStr(Row + 1)).Select
                    Selection.Cut Destination:=Rows(CStr(FirstRow) & ":" & CStr(FirstRow))
                    Rows(CStr(Row + 1) & ":" & CStr(Row + 1)).Select
                    Selection.Delete Shift:=xlUp
                End If
            Next
    OK, first some details:

    "For Each i In Content"
    Content is the selection of cells by the user. Every other piece of my code is working perfectly even the functions I wrote myself (FindWord). The only problem I have is, that at the end of the code at the last line (the "Next"), for some reason the next cell it goes to in the selection is 2 rows down, not one?... even stranger: It only goes down two rows when the "If InStr(UCase(i.Value), UCase(FindWord(sSearch, w, ","))) > 0 Then" is evaluated true. Every other time it only goes down one like it's meant to...

    Basically this is part of a script which searches through cells and moves up ones which contain certain words, specified by the user.

    Any help would be GREATLY appreciate, this is actually a script I have to write for the place I'm working at... I started learning VB only 2 days ago when they told me this script had to be written in VB.

    Basically I need it to only go one line down, not 2. Anyone know why it would be doing this, or how to stop it?

    PS: I tried taking the (Row + 1) out of the lines of code in the If section... didn't fix it, infact it broke the script altogether.

  2. #2
    Well I was able to come up with a solution, but I'm not very happy with it... The solution was to make this script go over this section for as many times as there is rows selected, so it now works, cause even though it misses a row every now and then, it goes over the rows again and doesn't miss it the second (or third, or fourth, or fifth...) time.

    Although this now means the script is exponentially slower... so slow that it probably won't be of much use for the company I'm making it for

    So any help would still be appreciated?

  3. #3
    Can you post what FindWord looks like and also where Trigger and sSearch are coming from?

  4. #4
    Here's one way to loop through the selected cells on the active worksheet

    Code:
    Sub Macro1()
    Dim rowCount As Integer
    Dim colCount As Integer
    Dim keywords(2) As String
    Dim i As Integer
    
    keywords(0) = "Red"
    keywords(1) = "Green"
    keywords(2) = "Blue"
    
    ' Loop through each row in the current selection
    For rowCount = 1 To Selection.Rows.Count
        ' Loop through each column
        For colCount = 1 To Selection.Columns.Count
            If Not IsEmpty(Selection.Cells(rowCount, colCount)) Then
                ' Find words in cell
                For i = 0 To UBound(keywords)
                    If InStr(1, Selection.Cells(rowCount, colCount).Value, keywords(i), vbTextCompare) Then
                        MsgBox ("Found keyword " & keywords(i))
                    End If
                Next i
            End If
        Next colCount
    Next rowCount
    End Sub

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012