Loop With If Then Statement

[mr_clean]

Hello, I am attempting to create a loop that reads a text in column AC (29) and if the text matches my criteria it creates a vlookup in column AJ (36) that looks like the below vlookup. I am looking for the search to start in row 65 of the spreadsheet and read till the last value in column AC (29).

Sub TheIfOrFunction()


Range("AC889").Select

'vlookup is placed in column (36) and reads the style number in column E (5) and returns the value from sheet 2
If Range("AC65").Value = "Jeans" Or Range("AC65").Value = "Shorts" Then


Range("AJ65").Value = "=VLOOKUP(RC[-31],'Sheet 2'!C[-32]:C[-10],23,FALSE)"


End If


End Sub

In my attempts to use a loop created by wideboydixon I tried to piece together the below loop but am currently failing.

Would anyone have any insight on how i can make this work? I am currently getting an error on the basevalue line

Public Sub FabricVLdetail()


Dim row As Long
Dim col As Long
Dim baseValue As Long


' This is the column number that contains the values
col = 29


' This is the row where we will start
row = 65


' Let's fetch the starting value
baseValue = (Cells(row, col).Value)


' Now we'll keep looping until we find a blank cell in the column
Do While Cells(row, 29).Value <> ""
' If the number in this cell is greater than we wanted

If (Cells(row, col).Value) = "Jeans" Then



Range(Cells(row, 36).Value) = "=VLOOKUP(RC[-31],'sheet 2'!C[-32]:C[-10],23,FALSE)"
Cells(row, col).Value = baseValue

End If

' Move to the next row
row = row + 1

' Increase the expected value for the next row
baseValue = baseValue + 1
Loop


End Sub


Thank you in advance

 

[KenCriss]

Maybe baseValue is returning something other than a number?

 

[bhos123]

Try this

Public Sub FabricVLdetail()


Dim row As Long
Dim col As Long
Dim baseValue As Long


' This is the column number that contains the values
col = 29


' This is the row where we will start
row = 65


' Let's fetch the starting value
baseValue = Cells(row, col).Value


' Now we'll keep looping until we find a blank cell in the column
Do While Cells(row, 29).Value <> ""
' If the number in this cell is greater than we wanted

If Cells(row, col).Value = "Jeans" Or Cells(row, col).Value = "Shorts" Then



Cells(row, 36).Value = "=VLOOKUP(RC[-31],'sheet 2'!C[-32]:C[-10],23,FALSE)"
'Cells(row, col).Value = baseValue

End If

' Move to the next row
row = row + 1

' Increase the expected value for the next row
'baseValue = baseValue + 1
Loop


End Sub



You are adding 1 to basevalue, sometimes basevalue can be "Jeans" and you are adding 1.

 

[mr_clean]

if the the criteria for the vlookup is jeans but the loop has to run through the following values how can i change the base value to read the following?

Coats

Jeans

Dress Shirt

Hats

Jackets

Polo Shirts

Pants

Shorts

Casual Shirt

Blazer

Sweaters

Swimsuit

T-shirts

Vests

Sweatshirt

<colgroup><col></colgroup><tbody>
</tbody>

if the the criteria for the vlookup is a text base do i need to put basevalue= Cells(row,Col).text then in the if statement put cells(row, col).value= "Jeans" ?

thank you.

 

[bhos123]

If Cells(row, col).Value = "Jeans" Or Cells(row, col).Value = "Shorts" Or cells(row,col).value = "Coats" Then

Keep adding more conditions to the above line. Hope this helps.