stupid me - search across row

stupid me - search across row

Post by U3RlcGhlb » Sat, 07 Jul 2007 00:16:02


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!
 
 
 

stupid me - search across row

Post by Bob Philli » Sat, 07 Jul 2007 00:23:35


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)

 
 
 

stupid me - search across row

Post by U3RlcGhlb » Sat, 07 Jul 2007 03:20:02

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
 
 
 

stupid me - search across row

Post by Bob Philli » Sat, 07 Jul 2007 21:29:15

Try cell.address

--
---
HTH

Bob

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

stupid me - search across row

Post by U3RlcGhlb » Sat, 07 Jul 2007 23:58:03

that's the one! thanks!