writing formula w/ variables

[Matthew Dyer]

This should be a simple solution but I can't figure it out... Here is
the line of code I'm having issues with -

.Formula = "=(((COUNT(b:b)+" & var1 & ")*5)-SUM(b:c))/" & var1 &"

Why isn't this working for me? I used the same basic "& _ &" roadmap
in the following code with no problem

Range("H7").Value = "In " & var1 & " Days:"

 

[Matthew Dyer]

This should be a simple solution but I can't figure it out... Here is
the line of code I'm having issues with -

    .Formula = "=(((COUNT(b:b)+" & var1 & ")*5)-SUM(b:c))/" & var1 &"

Why isn't this working for me? I used the same basic "& _ &" roadmap
in the following code with no problem

Range("H7").Value = "In " & var1 & " Days:"

I used a workaround where I just put the value of var1 in a cell and
referanced the cell instead of the variable, but I would definatley
appreciate anyone who could tell me what I'm doing wrong... Thanks!!!

 

[Don Guillett Excel MVP]

I used a workaround where I just put the value of var1 in a cell and
referanced the cell instead of the variable, but I would definatley
appreciate anyone who could tell me what I'm doing wrong... Thanks!!!

Try your formula on the worksheet>put an apostophe in front>copy to vb
editor>modidy

 

[FSt1]

This should be a simple solution but I can't figure it out... Here is
the line of code I'm having issues with -

    .Formula = "=(((COUNT(b:b)+" & var1 & ")*5)-SUM(b:c))/" & var1 &"

Why isn't this working for me? I used the same basic "& _ &" roadmap
in the following code with no problem

Range("H7").Value = "In " & var1 & " Days:"

hi
i noticed in your formula that the ampersand symbol(&) is WITHIN the
double quotes. this makes it text along with var1.
the ampersand symbol should ALWAYS be OUTSIDE of the double quotes ie
..Formula = "=(((COUNT(b:b) +" & var1 & ")*5)-SUM(b:c) )/ " & var1
the above is tested in 03 and works.
also I noticed that in your second example, the ampersands are
OUTSIDE of the double quotes as is var1.
if you have trouble understanding this, look at it this way....
Range("H7").Value = "Inside " & outside & " inside:"
the ampersands concatenates all of the text(numbers,other) between the
multiple double quotes.
anything WITHIN the double quotes is taken literally by excel(VB).
anything OUTSIDE the double quotes are declared variables. (you did
declare them and assign values, didn't you)

regards
FSt1

 

[mcescher]

This should be a simple solution but I can't figure it out... Here is
the line of code I'm having issues with -

    .Formula = "=(((COUNT(b:b)+" & var1 & ")*5)-SUM(b:c))/" & var1 &"

Why isn't this working for me? I used the same basic "& _ &" roadmap
in the following code with no problem

Range("H7").Value = "In " & var1 & " Days:"

Well, stubbed in this code

Dim rnga As Range, var1 As Double
Set rnga = Range("A1")
var1 = 8
With rnga
.Formula = "=(((COUNT(b:b)+" & var1 & ")*5)-SUM(b:c))/" & var1 & "
"
End With


and it worked fine. I did notice that Excel added another double
quote at the end. When I look at the formula it pasted into A1, I
get:
=(((COUNT(B:B)+8)*5)-SUM(B:C))/8

Perhaps you could give us a little more than one line of code.

Chris M.