Adding formulas via macro

  • Thread starter Thread starter mattg
  • Start date Start date
M

mattg

Hi,

I'm trying to add some formulas with a macro. With the first I want the
value of "D2" to be "XXX" if the 3 left characters of cell "C2" are "YYY" but
I'm not sure of the correct syntax. This is what I have so far:

If Left.Range("c2", 3) = "095" Then .Range("D" & 2).Value = "ZB900"
..Range("D" & 2).Copy _
Destination:=.Range("D" & 2 & ":D" & lstrw)

In this next case I'm trying to load the formula:

..Range("E" & 2).Formula = "=("0"&right(c2,4))"

but I keep getting an error.

Any ideas?
 
Do you want the macro to produce a formula that does the work or do you want the
macro to do the work?

If a formula:

with worksheets("Sheet9999") '<--what sheet???
.range("d2").formular1c1 _
= "=if(right(rc[-1],3)=""yyy"",""xxx"","""")"
end with

If you want the macro to do the work:

with worksheets("Sheet9999")
if lcase(right(.range("c2").value,3)) = "yyy" then
.range("d2").value = "XXX"
else
.range("D2").value = ""
end if
end with

I chose to make the cell look empty if the criterion wasn't met. I'm not sure
that's what you wanted.

=====
And try this:

.Range("E" & 2).Formula = "=(""0""&right(c2,4))"

Notice how the double quotes in strings in formulas in your code are doubled up.
 
I went with option 2 for the first macro and it worked great. The fix on the
second formula work also.

Thanks!!!!

Dave Peterson said:
Do you want the macro to produce a formula that does the work or do you want the
macro to do the work?

If a formula:

with worksheets("Sheet9999") '<--what sheet???
.range("d2").formular1c1 _
= "=if(right(rc[-1],3)=""yyy"",""xxx"","""")"
end with

If you want the macro to do the work:

with worksheets("Sheet9999")
if lcase(right(.range("c2").value,3)) = "yyy" then
.range("d2").value = "XXX"
else
.range("D2").value = ""
end if
end with

I chose to make the cell look empty if the criterion wasn't met. I'm not sure
that's what you wanted.

=====
And try this:

.Range("E" & 2).Formula = "=(""0""&right(c2,4))"

Notice how the double quotes in strings in formulas in your code are doubled up.
Hi,

I'm trying to add some formulas with a macro. With the first I want the
value of "D2" to be "XXX" if the 3 left characters of cell "C2" are "YYY" but
I'm not sure of the correct syntax. This is what I have so far:

If Left.Range("c2", 3) = "095" Then .Range("D" & 2).Value = "ZB900"
.Range("D" & 2).Copy _
Destination:=.Range("D" & 2 & ":D" & lstrw)

In this next case I'm trying to load the formula:

.Range("E" & 2).Formula = "=("0"&right(c2,4))"

but I keep getting an error.

Any ideas?
Click to expand...
Click to expand...
 
Back
Top