stupid me - search across row
 

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
 

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)



"Stephen" wrote in message
...
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
 

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" wrote:


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)



"Stephen" wrote in message
...
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
 

Try cell.address

--
---
HTH

Bob

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



"Stephen" wrote in message
...
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" wrote:


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)



"Stephen" wrote in message
...
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
 

that's the one! thanks!

"Bob Phillips" wrote:

Try cell.address

--
---
HTH

Bob

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



"Stephen" wrote in message
...
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" wrote:


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)



"Stephen" wrote in message
...
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!