Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formula w/ variables
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:" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formula w/ variables
On Jun 30, 1:24*pm, Matthew Dyer wrote:
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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formula w/ variables
On Jun 30, 6:38*pm, Matthew Dyer wrote:
On Jun 30, 1:24*pm, Matthew Dyer wrote: 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!!! Try your formula on the worksheetput an apostophe in frontcopy to vb editormodidy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formula w/ variables
On 30 June, 16:24, Matthew Dyer wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formula w/ variables
On Jun 30, 3:24*pm, Matthew Dyer wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing a variables value every second to a table | Excel Programming | |||
help in writing a formula | Excel Worksheet Functions | |||
writing a formula | Excel Discussion (Misc queries) | |||
Need help with writing formula please... | Excel Worksheet Functions | |||
writing cells with variables from vba | Excel Programming |