Paul Collins
asked on
Excel VBA Variable for R or C in an equation
below is a VBA formula for subtotalling filtered data and which works fine if I define the column number as a given number in in the section of code to the right
However I would like to define the column number as a variable ie Col Number 2 or Column Number 6 etc
Have seen some suggestions how this might work but doesnt seem to like the formula I am using
I know what I have put in is wrong but am trying to figure out the correct if possible version
Sub Analysis_Testdev()
Dim ColNumberx As Integer
ColNumberx = 2
Sheets("WsheetA").Select
Range("B10").Select
Application.CutCopyMode = False
'
ActiveCell.FormulaR1C1 = _
"=IF('123WsheetA'!RC[-1]>-
' my problem is getting this bit of the above equation to work in that I want the Column Number to be a variable ColNumberx and i cant seem to get it to work
' --('WsheetA'![R49C&ColNumb
' the aim is to create a valid formula in B10 based on whatever column I choose and then copy it to cells B10:B29 but I cant get the column number based on a variable right
Range("B10").Select
Selection.Copy
Range("B10:B29").Select
ActiveSheet.Paste
End Sub
However I would like to define the column number as a variable ie Col Number 2 or Column Number 6 etc
Have seen some suggestions how this might work but doesnt seem to like the formula I am using
I know what I have put in is wrong but am trying to figure out the correct if possible version
Sub Analysis_Testdev()
Dim ColNumberx As Integer
ColNumberx = 2
Sheets("WsheetA").Select
Range("B10").Select
Application.CutCopyMode = False
'
ActiveCell.FormulaR1C1 = _
"=IF('123WsheetA'!RC[-1]>-
' my problem is getting this bit of the above equation to work in that I want the Column Number to be a variable ColNumberx and i cant seem to get it to work
' --('WsheetA'![R49C&ColNumb
' the aim is to create a valid formula in B10 based on whatever column I choose and then copy it to cells B10:B29 but I cant get the column number based on a variable right
Range("B10").Select
Selection.Copy
Range("B10:B29").Select
ActiveSheet.Paste
End Sub
ASKER
Short answer is No but your response seems to have two different treatments of the variable
f
-('WsheetA'![R49C&ColNumbe
ie ![R49C&ColNumberx] and then [R20000C" & ColNumberx & "]
was that intentional
Thanks Paul
f
-('WsheetA'![R49C&ColNumbe
ie ![R49C&ColNumberx] and then [R20000C" & ColNumberx & "]
was that intentional
Thanks Paul
Hi,
You need to concatenate the column number in your formula (notice the additional quotes):
Additional notes:
- Never use objects such as ActiveWorkbook, ActiveSheet, ActiveShape, Selection, Range (alone) as their value depend on user action, and are by nature chaotic.
As a developper, you don't want to use chaotic objects.
Prefer referencing directly and precisely the objects you need.
- Avoid selecting ranges, it is slow as hell.
Your code can easily be replaced by:
You need to concatenate the column number in your formula (notice the additional quotes):
ActiveCell.FormulaR1C1 = _
"=IF('123WsheetA'!RC[-1]>-.1,SUMPRODUCT(SUBTOTAL(109,OFFSET('123WsheetA'!R48C5,ROW('WsheetA'!R49C5:R20000C5)-ROW('WsheetA'!R48C5),,1)),--('WsheetA'![R49C" & ColNumberx & "]:[R20000C" & ColNumberx & "]='WsheetA'!RC[-1])),"""")"Additional notes:
- Never use objects such as ActiveWorkbook, ActiveSheet, ActiveShape, Selection, Range (alone) as their value depend on user action, and are by nature chaotic.
As a developper, you don't want to use chaotic objects.
Prefer referencing directly and precisely the objects you need.
- Avoid selecting ranges, it is slow as hell.
Your code can easily be replaced by:
Sub Analysis_Testdev()
Dim ColNumberx As Integer
Dim ws as Excel.Worksheet
Dim rng as Excel.Range
ColNumberx = 2
Set ws = ThisWorkbook.WorkSheets("WsheetA")
Set rng = Range("B10")
Application.CutCopyMode = False
rng.FormulaR1C1 = '// your formula here
Set rng = ws.Range("B10")
rng.Copy ws.range(""B10:B29")SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- Paul Collins (https:#a42371694)
-- Subodh Tiwari (Neeraj) (https:#a42370517)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- Paul Collins (https:#a42371694)
-- Subodh Tiwari (Neeraj) (https:#a42370517)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
pls try
Open in new window
Regards