proper syntax for With Rng/Evaluate?

[ilcaa]

i have a range(1000 rows) that i want to apply a change to. Its basically taking a variable and placing at the front of each string along with a comma. here is a before and after

6 of 1998 3-0-1 100.00% 14-18-2 43.75%
36, 6 of 1998 3-0-1 100.00% 14-18-2 43.75%

variable was 36 in this case. I though it was simple enough, but i get a 'Type MisMatch' error.

What is proper syntax, would EVALUATE help out?? thanks

Formula = 36

With Rng
    .Value = Formula & "," & .Value
End With

 

[James006]

Hello,

No Evaluate would not help ...

You can test following

Sub Check()
    Dim rng As Range
    Dim formula As String
    formula = 36
    Set rng = ActiveCell
    With rng
        .Value = formula & "," & .Value
    End With
End Sub

Hope this will help

 

[Michael M]

I think you are also asking for problems, If you are assigning a number to a variable called "formula"
Call it n, or x, or anything but using the word formula.

 

[footoo]

Dim f$: f = "36, "
Dim rng As Range: Set rng = [A1:A8]
rng = Application.Evaluate("concatenate(" & f & """, ""," & rng.Address & ")")

 

[ilcaa]

Hello,

No Evaluate would not help ...

You can test following

Sub Check()
    Dim rng As Range
    Dim formula As String
    formula = 36
    Set rng = ActiveCell
    With rng
        .Value = formula & "," & .Value
    End With
End Sub

Hope this will help

thanks. if i use this on a single cell it works fine. if i use this on a range of cells, i get the same mismatch error. if you notice my original post, it has the same formula,

with rng
   .value = formula & "," & .value
end with

my range is 1000 rows and i need to adds this variable to each row, I do not want to do a loop and trying to find the right syntax for With Range.

 

[Fluff]

How about

Sub ilcaa()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("If({1},""36, ""&" & .Address & ")")
   End With
End Sub

 

[ilcaa]

How about

Sub ilcaa()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("If({1},""36, ""&" & .Address & ")")
   End With
End Sub

thanks mr fluffer, that worked.

 

[ilcaa]

i had a question, this symtax is quite confusing between all the quotes. if my value 36 is actually a variable named 'strategy', how would i incorporate that into this evaluate? i tried several ways but get the #NAME ? error

 

[Fluff]

You're welcome & thanks for the feedback

 

[ilcaa]

How about

Sub ilcaa()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("If({1},""36, ""&" & .Address & ")")
   End With
End Sub

to be more speciifc and follow up on my reply..

instead of a hard coded 36.
strategy = 36

how would the evaluate look then (ive tried multiple ways but between all the quotes and & ai cant figure out the syntax