Issue using iRow

**faoltaem** · 11-14-2012, 09:32 PM

Hi,

Having an issue with a VBA formula. The formula will work while I have a specific cell identified (eg. J2), but when I elect for the row number to be ‘iRow’ it mysteriously won’t work. Just to clarify I have the parameters for ‘iRow’ set up, and they do work with other formulas I have running just not this one.

The broken code:

Ws.Cells(iRow, 21).Formula = "=IF($J” & iRow & “=""-"",IF($X” & iRow & “=""-"",""No Data"",IF($X” & iRow & “<$A$3,""Expired"",IF($X” & iRow & “<$A$4,IF(OR(ISNUMBER(SEARCH(""O"",$W” & iRow & “)),ISNUMBER(SEARCH(""P"",$W” & iRow & “))),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$ W” & iRow & “)),ISNUMBER(SEARCH(""P"",$W” & iRow & “))),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""IRB Only"",""Invalid""))))),IF($M” & iRow & “<16,""Underage"",IF($X” & iRow & “=""-"",""No Data"",IF($X” & iRow & “<$A$3,""Expired"",IF($X” & iRow & “<$A$4,IF(OR(ISNUMBER(SEARCH(""O"",$W” & iRow & “)),ISNUMBER(SEARCH(""P"",$W” & iRow & “))),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$ W” & iRow & “)),ISNUMBER(SEARCH(""P"",$W” & iRow & “))),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W” & iRow & “)),""IRB Only"",""Invalid"")))))))"

Other codes that do work:
(basically the same thing, just 2 instead of iRow)

Ws.Cells(iRow, 21).Formula = "=IF($J2=""-"",IF($X2=""-"",""No Data"",IF($X2<$A$3,""Expired"",IF($X2<$A$4,IF(OR(I SNUMBER(SEARCH(""O"",$W2)),ISNUMBER(SEARCH(""P"",$ W2))),IF(ISNUMBER(SEARCH(""RMDL"",$W2)),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W2)),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$ W2)),ISNUMBER(SEARCH(""P"",$W2))),IF(ISNUMBER(SEAR CH(""RMDL"",$W2)),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W2)),""IRB Only"",""Invalid""))))),IF($M2<16,""Underage"",IF( $X2=""-"",""No Data"",IF($X2<$A$3,""Expired"",IF($X2<$A$4,IF(OR(I SNUMBER(SEARCH(""O"",$W2)),ISNUMBER(SEARCH(""P"",$ W2))),IF(ISNUMBER(SEARCH(""RMDL"",$W2)),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W2)),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$ W2)),ISNUMBER(SEARCH(""P"",$W2))),IF(ISNUMBER(SEAR CH(""RMDL"",$W2)),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W2)),""IRB Only"",""Invalid"")))))))"

&
(test that iRow is working)

ws.Cells(iRow, 21).Formula = "=IF(ISNUMBER(SEARCH(""O"",$W" & iRow & ")),""True"",""False"")"

Any ideas?

**Norie** · 11-14-2012, 09:39 PM

You have a space before one of the Ws here.

Formula:



IF(OR(ISNUMBER(SEARCH(""O"",$ W” & iRow & “)),

**protonLeah** · 11-14-2012, 09:49 PM

You have some characters: ” & “ in your code that are not quotation marks (")

**faoltaem** · 11-14-2012, 10:27 PM

Thanks, still not working for me, fixed the spaces:

Ws.Cells(iRow, 21).Formula = "=IF($J" & iRow & "=""-"",IF($X" & iRow & "=""-"",""No Data"",IF($X" & iRow & "<$A$3,""Expired"",IF($X" & iRow & "<$A$4,IF(OR(ISNUMBER(SEARCH(""O"",$W" & iRow & ")),ISNUMBER(SEARCH(""P"",$W" & iRow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$W" & iRow & ")),ISNUMBER(SEARCH(""P"",$W" & iRow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""IRB Only"",""Invalid""))))) _
            ,IF($M" & iRow & "<16,""Underage"",IF($X" & iRow & "=""-"",""No Data"",IF($X" & iRow & "<$A$3,""Expired"",IF($X" & iRow & "<$A$4,IF(OR(ISNUMBER(SEARCH(""O"",$W" & iRow & ")),ISNUMBER(SEARCH(""P"",$W" & iRow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$W" & iRow & ")),ISNUMBER(SEARCH(""P"",$W" & iRow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & iRow & ")),""IRB Only"",""Invalid"")))))))"

The " & iRow & " is still there because that's how iRow works. And I've had to break it over two lines since it doesn't fit on one.

The error I get says:
Compile error:
Expected: end of statement

**Norie** · 11-14-2012, 10:30 PM

To continue a string on a new line you need to concatenate.

ws.Cells(irow, 21).Formula = "=IF($J" & irow & "=""-"",IF($X" & irow & "=""-"",""No Data"",IF($X" & irow & "<$A$3,""Expired"",IF($X" & irow & "<$A$4,IF(OR(ISNUMBER(SEARCH(""O"",$W" & irow & ")),ISNUMBER(SEARCH(""P"",$W" & irow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$W" & irow & ")),ISNUMBER(SEARCH(""P"",$W" & irow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""IRB Only"",""Invalid"")))))" & _
            ",IF($M" & irow & "<16,""Underage"",IF($X" & irow & "=""-"",""No Data"",IF($X" & irow & "<$A$3,""Expired"",IF($X" & irow & "<$A$4,IF(OR(ISNUMBER(SEARCH(""O"",$W" & irow & ")),ISNUMBER(SEARCH(""P"",$W" & irow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""(R) ATV & IRB"",""(R) ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""(R) IRB Only"",""Invalid"")),IF(OR(ISNUMBER(SEARCH(""O"",$W" & irow & ")),ISNUMBER(SEARCH(""P"",$W" & irow & "))),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""ATV & IRB"",""ATV Only""),IF(ISNUMBER(SEARCH(""RMDL"",$W" & irow & ")),""IRB Only"",""Invalid"")))))))"