writing formulas in VB - referring to the values contained in cell

[Josie]

I'm new to writing code from scratch and I'm struggling with something.

Basically I have a row of numbers and beneath it I need a row which is the %
value that the number above is of the leftmost number. ie:

50 42 8
84 16

The row is a different number on every sheet so I can't use specific cell
references. I've managed to name all the cells that's not the problem, but
I'm unsure of the syntax for the mathmatical formula.

per1 = (p2 / (p1 / 100)) where p2 and p1 are names of cells, and per1 is a
string isn't working.

[Josie]

OK, I've realised that wasn't very clear. I've made some progress actually

-irrelevant code-
Dim per1 As String
per1 = "=sum(" & r3 & ":" & r4 & ")/(=sum(" & r1 & ":" & r2 & ")/100)"
ActiveCell.formula = per1

It's sticking at the final line. When you look at the Excel file, the
correct cell is selected, but the value's not there.

"Josie" wrote:

> I'm new to writing code from scratch and I'm struggling with something.
>
> Basically I have a row of numbers and beneath it I need a row which is the %
> value that the number above is of the leftmost number. ie:
>
> 50 42 8
> 84 16
>
> The row is a different number on every sheet so I can't use specific cell
> references. I've managed to name all the cells that's not the problem, but
> I'm unsure of the syntax for the mathmatical formula.
>
> per1 = (p2 / (p1 / 100)) where p2 and p1 are names of cells, and per1 is a
> string isn't working.

[Udo]

Hi Josie,

given your r1 through r4 are variables, the following should work:
Dim R1 As String
Dim R2 As String
Dim R3 As String
Dim R4 As String
Dim per1 As String

R1 = "B4"
R2 = "H4"
R3 = "B8"
R4 = "H5"

per1 = "=sum(" & R3 & ":" & R4 & ")/(sum(" & R1 & ":" & R2 & ")/100)"
ActiveCell.Formula = per1

Good luck
Udo

[Josie]

Thanks Udo, but it's not working. I have a friend who's relatively good at
this stuff, and he can't figure out what's wrong either. Driving me CRAZY.

"Udo" wrote:

> Hi Josie,
>
> given your r1 through r4 are variables, the following should work:
> Dim R1 As String
> Dim R2 As String
> Dim R3 As String
> Dim R4 As String
> Dim per1 As String
>
> R1 = "B4"
> R2 = "H4"
> R3 = "B8"
> R4 = "H5"
>
> per1 = "=sum(" & R3 & ":" & R4 & ")/(sum(" & R1 & ":" & R2 & ")/100)"
> ActiveCell.Formula = per1
>
> Good luck
> Udo
>
>

[ers]

Hi Josie,

for few days I was looking for a way to reference in VBA a cell in
another sheet.
Only after I red this posting(s) I figure out the proper syntax. It
wasn't working when the reference cell was in another workbook.
The following is just to select a cell in sheet "c" knowing the column
which is mentionated in a cell (A1) in sheet "a".
Maybe will help in your situation.

Sub findcell()
Dim CN
CN = ThisWorkbook.Sheets("a").Range("A1").Value
'
Sheets("c").Select
Cells(1, CN).Select
End Sub

Good Luck
emil