Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by awesomejohn19
at 2024-04-25 09:27:47
ModifyPoint:500 Replies:8 POST_ID:828485USER_ID:11311
Topic:
Microsoft Excel Spreadsheet Software;;Visual Basic Programming
I want to write formulas with vba on a spreadsheet. I couldnt make the following work.
So there is a for statement which is supposed to from j=0 to 10. So on each ("C10"), I would like to write the formula ("C11").offset(0,j)/("C7").offset(0,j). So again, I dont want the exact number but I would like to have the formula on that cell ("C10")
I tried the following but it doesnt work.
Sheets("Sheet3").Range("C10").Offset(0, j).Formula = "=(Sheet3!C"&," & Sheets("Sheet3").Range("C11").Offset(0, j).Value & "/" & Sheets("Sheet3").Range("C7").Offset(0, j).
So there is a for statement which is supposed to from j=0 to 10. So on each ("C10"), I would like to write the formula ("C11").offset(0,j)/("C7").offset(0,j). So again, I dont want the exact number but I would like to have the formula on that cell ("C10")
I tried the following but it doesnt work.
Sheets("Sheet3").Range("C10").Offset(0, j).Formula = "=(Sheet3!C"&," & Sheets("Sheet3").Range("C11").Offset(0, j).Value & "/" & Sheets("Sheet3").Range("C7").Offset(0, j).
Expert: bromy2004 replied at 2024-06-08 01:20:51
VBA Example-1
Set rg = Cells(1, m.Column)
Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"
Example-2
range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"
You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
Set rg = Cells(1, m.Column)
Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"
Example-2
range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"
You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
Expert: bromy2004 replied at 2024-06-03 18:24:17
VBA Example-1
Set rg = Cells(1, m.Column)
Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"
Example-2
range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"
You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
Set rg = Cells(1, m.Column)
Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"
Example-2
range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"
You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
Expert: duncanb7 replied at 2024-05-05 11:43:27
VBA Example-1
Set rg = Cells(1, m.Column)
Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"
Example-2
range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"
You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
Set rg = Cells(1, m.Column)
Set rg = Range(rg, Cells(l1000, rg.Column))
rg.Offset(0, 0).FormulaR1C1 = "=if(rc[" & l.Column - p.Column & "]>0,rc[" & l.Column - m.Column & "],0)"
Example-2
range("A1")= "=" & sheetname & "!R" & firstrow & "C4:R" & lastrow & "C4"
range("A2") = "=" & sheetname & "!R" & firstrow & "C2:R" & lastrow & "C2"
You can switch A1,B1, to RC in your excel shhet from tool--> option-->Genernal
Accepted Solution
Expert: Rory Archibald replied at 2024-04-26 00:16:27
500 points EXCELLENT
You could also use R1C1 style:
Sheets("Sheet3").Range("C10").Offset(0, j).FormulaR1C1 = "=R[1]C/R[-3]C"
Sheets("Sheet3").Range("C10").Offset(0, j).FormulaR1C1 = "=R[1]C/R[-3]C"
Expert: damerval replied at 2024-04-25 11:53:17
You write "on each C10". There is only one C10. Am I understanding correctly that you want to set formulae horizontally from left to right, updating the column letter as you move to the right? It is easiest to manually drag the formula on the far left , excel will automatically fill cells to the right accordingly, updating the formula as it goes. To do this in code, try something like this (using the integer values for the column letters):
For Col = Asc("C") To Asc("D")
Sheets("Sheet1").Range(Chr(Col) & "10").Formula = "=" & Chr(Col) & "7/" & Chr(Col) & "11"
Next Row
For Col = Asc("C") To Asc("D")
Sheets("Sheet1").Range(Chr(Col) & "10").Formula = "=" & Chr(Col) & "7/" & Chr(Col) & "11"
Next Row
Author: awesomejohn19 replied at 2024-04-25 11:41:48
Ok, this worked
Sheets("Sheet3").Range("C10").Offset(0, j).Formula = "=" & Sheets("Sheet3").Range("C11").Offset(0, j).Address & "/" & Sheets("Sheet3").Range("C7").Offset(0, j).Address
Sheets("Sheet3").Range("C10").Offset(0, j).Formula = "=" & Sheets("Sheet3").Range("C11").Offset(0, j).Address & "/" & Sheets("Sheet3").Range("C7").Offset(0, j).Address
Author: awesomejohn19 replied at 2024-04-25 11:24:14
Thanks for your response. Are you sure this is correct? When I do this I will get someting like C11.offset(0,J) which doesnt seem to be a valid worksheet function. When I type this I get #Name (I use C11.offset(0,2) for example. But what I need is just C11/C7. then D11/D7 for example.
Maybe I didnt understand what you mean completely. I am little confused :)
Maybe I didnt understand what you mean completely. I am little confused :)
Expert: damerval replied at 2024-04-25 10:13:48
Hi,
You have the right idea with the formula property. However, you need to set it as a string, which means that if your string contains double quotes, you need to double them up like this ("") so that they end up as double quotes in your string. Something like this:
Formula = "(""C11"").offset(0," & j & ")/(""C7"").offset(0," & j & ")"
HTH
Philippe
You have the right idea with the formula property. However, you need to set it as a string, which means that if your string contains double quotes, you need to double them up like this ("") so that they end up as double quotes in your string. Something like this:
Formula = "(""C11"").offset(0," & j & ")/(""C7"").offset(0," & j & ")"
HTH
Philippe
