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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
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.
 
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
 
Back
Top