loop until last row in spreadsheet

[mgrands]

Hello..

I have been struggling with this all day and would like some help. I would like to take a spreadsheet with anywhere from 500 - ? number of rows. I want the macro to look in column u for the word "dog" and put the word "bark" in column v. I have this part working in a macro. Now I want the macro to loop through all of the rows in the spreadsheet and do the same thing.

For some reason I can not get this to work. I have copied many lines of code and different suggestions from the internet and none of them loop to the end and do the procedure over and over.

Please help.

 

[VoG]

Try

Sub Test()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "U").End(xlUp).row
For i = 1 To LastRow
    If Range("U" & i).Value = "dog" Then Range("V" & i).Value = "bark"
Next i
End Sub

 

[mgrands]

I tried that and nothing happens. Do you have any other suggestions?

 

[mgrands]

There are some blank cells in column U before you get to the end. Would it be easier to reference column a for the loop as I know there is always a value in column a...

 

[VoG]

You must have the relevant sheet selected before you run the code. Blank cells shouldn't matter as long as column U has some values in it. It won't do anything unless it finds 'dog'.

 

[mgrands]

This is the code that I have and still nothing. (by the way I replaced dog and bark with the actual words for my file)

Dim WB As Workbook

Dim SH As Worksheet
Dim LastRow As Long, i As Long
Set WB = Workbooks("54602 Jan sample 2.xls")

Set SH = WB.Sheets("54602 Jan")
LastRow = Cells(Rows.Count, "u").End(xlUp).Row
For i = 1 To LastRow
If Range("U" & i).Value = "foliaire" Then Range("v" & i).Value = "RMPM"
Next i
End Sub

What do you think I am doing wrong?

 

[mgrands]

Can anyone else help on this topic or should I repost it.

I am basically just trying to loop through a spreadsheet and do a macro until the last row of data.

If this can not work - maybe a different approach. At the end of the day I want a document to lookup up 20 different words in column U and put a specific 4 digit code in column V. There are many instances of the same word and the document is around 4,000 rows long.

Any help would be appreciated.

 

[ravishankar]

Hi
perhaps a VLOOKUP may do the trick.
col V = Vlookup(U1, Y1:Z20,2,false)
list 20 search words in col Y and their equivalents in col Z
Drag the formula till the end of data
Ravi

 

[tusharm]

You set SH to a particular worksheet. But, then, you don't use SH in any subsequent statement. For more on what happens when you refer to Cells() or Range() without specifying the associated worksheet see
Case Study – Understanding code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm

This is the code that I have and still nothing. (by the way I replaced dog and bark with the actual words for my file)

Dim WB As Workbook

Dim SH As Worksheet
Dim LastRow As Long, i As Long
Set WB = Workbooks("54602 Jan sample 2.xls")

Set SH = WB.Sheets("54602 Jan")
LastRow = Cells(Rows.Count, "u").End(xlUp).Row
For i = 1 To LastRow
If Range("U" & i).Value = "foliaire" Then Range("v" & i).Value = "RMPM"
Next i
End Sub

What do you think I am doing wrong?