VBA Find using "i" in a For loop

[KWill]

Hello All,

(first post) I am new to using VBA and extremely rusty when it comes to any type of code. I only did a few semester worth of coding in college and I'm lost at this point.

I am starting a personal project for work and I'm already getting stuck on some of the basics. I am currently trying to use a For loop to look through a column and locate cells that would be 100, 110, 120, 200, 210, etc. There are multiple blank lines between these number so I was trying to make a formula that will locate these numbers. I hand planned to use this function multiple times to pull various data from my worksheet and input them into another.

My idea was to use a simple Find function but it appears i cannot perform a search for a number when "i" is used. I was hoping I could get code to work similar to the following.

Dim i As Long​

Dim numb As Long​

For i = 1 To 50
numb = i * 100
​

Cells.Find(What:=numb, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _​

xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _​

, SearchFormat:=False).Activate
Next i
​

I'm not far along but helping me understand this part will help me accomplish a majority of what I want to do.

Thank you for your help!

 

[Norie]

What are you going to do when you find the numbers?

 

[DataBlake]

100, 110, 120, 200, 210, etc.

for these numbers you would want to set your numb to the lowest common denominator of these numbers which i'm guessing would be 10 not 100
you also have to add error handling

Sub boop()

Dim i As Long
Dim numb As Long


For i = 1 To 50
numb = i * 10
On Error Resume Next
Cells.Find(What:=numb, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
On Error GoTo 0
Next i

End Sub

so this will loop 50 times
50 x 10 is 500
you will find numbers divisible by 10 up to 500
makes sense?

change depending on your needs
PS: while you are in the developer screen you can use F8 to go line by line and see what the code is doing

 

[KWill]

What are you going to do when you find the numbers?

For this particular search I will be extracting an adjacent cell. This will be item 100, sometimes there is an sub items 110 and item 120 (I think 140 will be the max). Then we have item 200 with potential for sub items.

My first task was to locate and pull all main item names (cells adjacent to the 100, 200, etc).

for these numbers you would want to set your numb to the lowest common denominator of these numbers which i'm guessing would be 10 not 100
you also have to add error handling

Sub boop()

Dim i As Long
Dim numb As Long


For i = 1 To 50
numb = i * 10
On Error Resume Next
Cells.Find(What:=numb, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
On Error GoTo 0
Next i

End Sub

so this will loop 50 times
50 x 10 is 500
you will find numbers divisible by 10 up to 500
makes sense?

change depending on your needs
PS: while you are in the developer screen you can use F8 to go line by line and see what the code is doing

Thank you for that suggestion! I hadn't thought of manipulating it that way. I will see how far I can take this!

 

[Repush]

maybe you can use a for-each-loop on a range instead, like:

For Each c In MyRange
        If c.Value Mod 10 = 0 then {do something}
    Next c

 

[DataBlake]

This should do what you want it to do (expanding on Repush's suggestion) which uses the equivalent of Mod to extract anything divisible by 10 and the adjacent cell (cell A for the part number Cell B for the item name) in a new sheet.

Sub boop()

Dim i As Variant
Dim latRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim destRow As Long

    'create and set sheets
    Set ws1 = ActiveSheet
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Range("A1").Select
    Set ws2 = ActiveSheet
    ws1.Activate

'define rows and ranges
lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row

'loop until the end of column a by row
For i = 1 To lastRow
    
    'if column a is divisable by 10 then post results
    On Error Resume Next
    If ws1.Cells(i, 1) - (10 * (ws1.Cells(i, 1) \ 10)) = 0 Then
    On Error GoTo 0
        destRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
        ws2.Range("A" & destRow).Value = ws1.Cells(i, 1).Value
        ws2.Range("B" & destRow).Value = ws1.Cells(i, 2).Value
    End If
        
Next i

End Sub

if you have the item name in a different column just change the red/bold value to represent the column

ws2.Range("B" & destRow).Value = ws1.Cells(i, [B][COLOR=#ff0000]2[/COLOR][/B]).Value

it would be easier to just cut and paste the item numbers in column A than changing the code, but if you wanted to you would change all the "(i,1)"s and "A"s to the proper columns

 

[Norie]

Are the values you are searching for all in the same column?