Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formulas in VB - referring to the values contained in cell
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formulas in VB - referring to the values contained in cell
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formulas in VB - referring to the values contained in cell
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formulas in VB - referring to the values contained in
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing formulas in VB - referring to the values contained in
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a list of formulas contained in a Workbook | Excel Discussion (Misc queries) | |||
Referring to sheets in formulas | Excel Discussion (Misc queries) | |||
Sum values referring to a cell with same value | Excel Discussion (Misc queries) | |||
how can I imbed sheet names contained in cells into formulas? | Excel Worksheet Functions | |||
referring to other cells with formulas | Excel Worksheet Functions |