VBA excel 2007 Transfer a Cell's value with a condition -

VBA excel 2007 Transfer a Cell's value with a condition -

in worksheet 'hitterscalc' cell d2, want paste value of worksheet 'batters' cell ks2 if value in 'hitterscalc' cell ab2 = 1

i think want of rows have info in worksheet 'hitterscalc'

i have come

with worksheets("hitterscalc") .range("d2:d" & .range("a" & rows.count).end(xlup).row) .formula = "=if($ab2=1,batters!ks2,""))" .value = .value end end

but returning application defined or object defined error.

can point me in right direction fixing issue?

edit:

so when do

with worksheets("hitterscalc") .range("d2:d" & .range("ab" & rows.count).end(xlup).row) .formula = "=batters!ks2" .value = .value end end

the look works properly. how can checks cell value of worksheet hitterscalc column ab first?

edit 2

with worksheets("hitterscalc") .range("d2:d" & .range("ab" & rows.count).end(xlup).row) .formula = "=if(and(a2<>"""",ab2=1),batters!ks2,"""")" '.formula = "=batters!ks2" .value = .value end end

works. confused why 1 works not first one.

i think confusion because of quotes - seek utilize code:

with worksheets("sheet1") .range("d2:d" & .range("ab" & rows.count).end(xlup).row) r = "=if($ab2=1,batters!ks2," & chr(34) & chr(34) & ")" .formula = r .value = .value end end

formula string generated using chr(34) double quotes used in if.

excel vba transfer