Trying to create formula w/ web address in VB

[boznian]

Using the following code to try and create a concatenated formula in VB so that I end up with a useable web address. I cannot get it to work and keep getting a "Type MisMatch" error:

        crow = ActiveCell.Row
        Cells(crow, "D:D").Select
        ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1], "" "",""-"")"
        Cells(crow, "B:B").Select
        ActiveCell.FormulaR1C1 = "=CONCATENATE(""http://www.webaddresshere.com/"",(crow, ""D:D"")," / ")"

The "substitute" formula works fine, why not the web address? I need to pull in the value in column D from the current row - am I going about it the wrong way?

 

[Rick Rothstein]

ActiveCell.FormulaR1C1 = "=CONCATENATE(""http://www.webaddresshere.com/"",(crow, ""D:D"")," / ")"

Assuming crow evaluates to 9, show us what you think this formula should look like when placed in the cell.

 

[boznian]

This is how I am doing it manually:

=CONCATENATE("http;//www.webaddress.com/",D9,"/")

 

[Rick Rothstein]

Give this a try...

ActiveCell.Formula = "=CONCATENATE(""http;//www.webaddress.com/"",D" & crow & ",""/"")"

Also note I am using the Formula property (because of the eventual D9 reference) instead of the FormulaR1C1 property (which expects the formula to be using R1C1 notation).

 

[boznian]

That did it! Thanks a million

 

[boznian]

Man, I am really awful at this. I really thought the next step would be easy once you gave me the previous answer, but now I can't get this to work either:

All I need now is to end up with the formula =LOWER(D32) where the "32" is determined by crow again. Tried every variatio of

         ActiveCell.Formula = "=LOWER(""D"", crowt)"

I can think of...

 

[Rick Rothstein]

Man, I am really awful at this. I really thought the next step would be easy once you gave me the previous answer, but now I can't get this to work either:

All I need now is to end up with the formula =LOWER(D32) where the "32" is determined by crow again. Tried every variatio of

         ActiveCell.Formula = "=LOWER(""D"", crowt)"

I can think of...

The thing to keep in mind is what you are after is a text string, so basically you just have to concatenate the parts you want together... and remember that variables cannot be inside the quote marks. Since this is a simple formula, let me show you how my thinking went. You start with this...

=LOWER(D32)

make it a text string...

"=LOWER(D32)"

separate the part that will be supplied by the variable crow...

"=LOWER(D" & 32 & ")"

and replace the number with the variable...

"=LOWER(D" & crow & ")"

Now put that together with the formula assignment statement..

ActiveCell.Formula = "=LOWER(D" & crow & ")"

 

[boznian]

Hey Rick-

Just wanted to say thanks again, and especially thanks for taking the time to explain it to me - now I get it!!

This is a wonderful place...