writing a formula in macro

[keyur]

hi

i got 2 problems

i want to add a Vloopup formula joined with IF statement
in the macro that should return " " if TRUE. i want to
have a space in between quotation marks (Reason. this file
is linked to an acess table where it will show #NUM if no
space)which gives an error"Expected:End of Statement". any
ideas how can i include this??

Second
this is kind of a continuation of my previous yesterday's
post "Help writing a macro". Here's what Trevor offered
(Thanks). i manipulated since i wanted column heading in
C1 and the formula starts from c2. For some reason it
gives me Description in C1 and rest all cells in C are
empty. any ideas?? any help is appreciated.

Columns("C").ClearContents
Range("C1").Formula = "Description"
Range("C2").Formula = "=IF(OR(ISNA(VLOOKUP(A2,'Parts
List'!$B$2:$D$6000,2,0)),_ISBLANK(VLOOKUP(A2,'Parts List'!
$B$2:$D$6000,2,0))),(""),VLOOKUP(A2,'Parts List'!
$B$2:$D$6000,2,0))"
Range("C2:C" & _
Application.WorksheetFunction.Max _
(Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)).FillDown

On Error Resume Next
Selection.SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

 

[Bob Phillips]

hi

i got 2 problems

i want to add a Vloopup formula joined with IF statement
in the macro that should return " " if TRUE. i want to
have a space in between quotation marks (Reason. this file
is linked to an acess table where it will show #NUM if no
space)which gives an error"Expected:End of Statement". any
ideas how can i include this??

Range("C2").Formula = "IF(NOT(ISNA(VLOOKUP_formula)),"" "", False_result)"

Second
this is kind of a continuation of my previous yesterday's
post "Help writing a macro". Here's what Trevor offered
(Thanks). i manipulated since i wanted column heading in
C1 and the formula starts from c2. For some reason it
gives me Description in C1 and rest all cells in C are
empty. any ideas?? any help is appreciated.

Columns("C").ClearContents
Range("C1").Formula = "Description"
Range("C2").Formula = "=IF(OR(ISNA(VLOOKUP(A2,'Parts
List'!$B$2:$D$6000,2,0)),_ISBLANK(VLOOKUP(A2,'Parts List'!
$B$2:$D$6000,2,0))),(""),VLOOKUP(A2,'Parts List'!
$B$2:$D$6000,2,0))"
Range("C2:C" & _
Application.WorksheetFunction.Max _
(Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)).FillDown

On Error Resume Next
Selection.SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

Is this not erroring because you seem to have the continuation characters
and line feeds seem mixed up. Either that, or the LOOKUP is blank or #N/A.

 

[Tom Ogilvy]

You can't put a line continuation character in the middle of a string.
("") won't put an empty string in your formula.

Sub tester2()
Dim sStr as String
Columns("C").ClearContents
Range("C1").Formula = "Description"
sStr = "=IF(OR(ISNA(VLOOKUP(A2,'Parts List'!$B$2:$D$6000,2,0))" _
& ",ISBLANK(VLOOKUP(A2,'Parts List'!$B$2:$D$6000,2,0))),"""",VLOOKUP("
& _
"A2,'Parts List'!$B$2:$D$6000,2,0))"
Range("C2:C" & _
Application.WorksheetFunction.Max _
(Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)) _
.Formula = sStr
On Error Resume Next
' Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

if you want to delete cells that contain a "", then your xlCelltypeBlanks
won't work, because those cells are not blank. I would change the formula
to

sStr = "=IF(OR(ISNA(VLOOKUP(A2,'Parts List'!$B$2:$D$6000,2,0))" _
& ",ISBLANK(VLOOKUP(A2,'Parts List'!$B$2:$D$6000,2,0))),NA(),VLOOKUP("
& _
"A2,'Parts List'!$B$2:$D$6000,2,0))"

then do

Columns(3).specialCells(xlFormulas, xlErrors).EntireRow.Delete

 

[keyur]

thanks a lot

-----Original Message-----
You can't put a line continuation character in the middle of a string.
("") won't put an empty string in your formula.

Sub tester2()
Dim sStr as String
Columns("C").ClearContents
Range("C1").Formula = "Description"
sStr = "=IF(OR(ISNA(VLOOKUP(A2,'Parts List'! $B$2:$D$6000,2,0))" _
& ",ISBLANK(VLOOKUP(A2,'Parts List'! $B$2:$D$6000,2,0))),"""",VLOOKUP("
& _
"A2,'Parts List'!$B$2:$D$6000,2,0))"
Range("C2:C" & _
Application.WorksheetFunction.Max _
(Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)) _
.Formula = sStr
On Error Resume Next
' Selection.SpecialCells (xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
End Sub

if you want to delete cells that contain a "", then your xlCelltypeBlanks
won't work, because those cells are not blank. I would change the formula
to

sStr = "=IF(OR(ISNA(VLOOKUP(A2,'Parts List'! $B$2:$D$6000,2,0))" _
& ",ISBLANK(VLOOKUP(A2,'Parts List'! $B$2:$D$6000,2,0))),NA(),VLOOKUP("
& _
"A2,'Parts List'!$B$2:$D$6000,2,0))"

then do

Columns(3).specialCells(xlFormulas, xlErrors).EntireRow.Delete

--
Regards,
Tom Ogilvy





.