stupid me - search across row

[Guest]

Hi Folks,

I'm feeling mighty sumb this morning cause I just can't figure out how to
loop across a row on one sheet from column AJ to column AS searching for non
zero values, then performing a vlookup to return a descrition which is
associated with that value, then starting the process on the next row until I
reach the last row of data.

Can someone help jump start my brain?

Thanks!

 

[Bob Phillips]

For i = 1 To Cells(Rows.Count,"AJ").End(xlUp).Row
For Each cell In Range("AJ" & i & ":AS" & i)
if cell.Value <> 0 Then
MsgBox Application.Vlookup(cell.Value,
Range("lookup_table"),2,False)
End If
Next cell
Next i

as a starter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Looks good with modifications for testing purposes, but ow can I get the
cell.value to return as AJ2, AJ3, AJ4, AH2, AH3 AH4, etc. in the loop. Right
now I have it returning the actual value of the cell which fails the lookup...


For j = 1 To Cells(Rows.Count, "AJ").End(xlUp).Row
For Each cell In Range("AJ" & j & ":AS" & j)
If cell.Value > 0 Then
Range("F" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & cell &
""",Garments,2,FALSE)"
Range("G" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=R[-13]C[7]"
Range("H" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=R[-13]C[8]"
End If
Next cell
Next j

 

[Bob Phillips]

Try cell.address

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Looks good with modifications for testing purposes, but ow can I get the
cell.value to return as AJ2, AJ3, AJ4, AH2, AH3 AH4, etc. in the loop.
Right
now I have it returning the actual value of the cell which fails the
lookup...


For j = 1 To Cells(Rows.Count, "AJ").End(xlUp).Row
For Each cell In Range("AJ" & j & ":AS" & j)
If cell.Value > 0 Then
Range("F" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & cell &
""",Garments,2,FALSE)"
Range("G" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=R[-13]C[7]"
Range("H" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=R[-13]C[8]"
End If
Next cell
Next j

For i = 1 To Cells(Rows.Count,"AJ").End(xlUp).Row
For Each cell In Range("AJ" & i & ":AS" & i)
if cell.Value <> 0 Then
MsgBox Application.Vlookup(cell.Value,
Range("lookup_table"),2,False)
End If
Next cell
Next i

as a starter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[Guest]

that's the one! thanks!

Try cell.address

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Looks good with modifications for testing purposes, but ow can I get the
cell.value to return as AJ2, AJ3, AJ4, AH2, AH3 AH4, etc. in the loop.
Right
now I have it returning the actual value of the cell which fails the
lookup...


For j = 1 To Cells(Rows.Count, "AJ").End(xlUp).Row
For Each cell In Range("AJ" & j & ":AS" & j)
If cell.Value > 0 Then
Range("F" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(""" & cell &
""",Garments,2,FALSE)"
Range("G" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=R[-13]C[7]"
Range("H" & NLastWearerRow + 2).Select
ActiveCell.FormulaR1C1 = "=R[-13]C[8]"
End If
Next cell
Next j

For i = 1 To Cells(Rows.Count,"AJ").End(xlUp).Row
For Each cell In Range("AJ" & i & ":AS" & i)
if cell.Value <> 0 Then
MsgBox Application.Vlookup(cell.Value,
Range("lookup_table"),2,False)
End If
Next cell
Next i

as a starter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Folks,

I'm feeling mighty sumb this morning cause I just can't figure out how
to
loop across a row on one sheet from column AJ to column AS searching
for
non
zero values, then performing a vlookup to return a descrition which is
associated with that value, then starting the process on the next row
until I
reach the last row of data.

Can someone help jump start my brain?

Thanks!