How do you convert an integer to a column location for a string variable in a Range selection

[stephicohu]

I hope the title makes sense. As I have stated, I am a self learner, with about 3 weeks of experience. I am having problems in using a cell location as a variable. I know you have to use a string expression but what I want to do is to convert a numeric number into a column cell location. For example if I have AG3 how do I convert this from an integer to a string expression. I need to know because I am using AH3 as the next expression getting a formula.

This is an example of the code I am doing:

Dim col1 As Integer
Dim cellnum As Integer
Dim col2 As Integer
Dim cellstr As String


cellnum = 33
cellstr = CStr(cells(33,3))

col1 = -15
col2 = -10

Sheets("January").Activate



Range(cellstr).Select

ActiveCell.FormulaR1C1 = "=SUM(RC[" & CStr(col1) & "]:RC[" & CStr(col2) & "])"

The trouble is converting the "AG3" from an integer value to this value.

I have tried many things but they failed. The best attempt I thought of was using the above value but it failed. I get a error on debug stating global error. I would appreciate anyones help in this matter.



Thanks....

Stephanie.....

 

[frabulator]

The trouble is converting the "AG3" from an integer value to this value.

I have tried many things but they failed. The best attempt I thought of was using the above value but it failed. I get a error on debug stating global error. I would appreciate anyones help in this matter.



Thanks....

Stephanie.....

You can use .Address

Here is an example

Sub GetAddress()

    MsgBox (ActiveSheet.Cells(1, 1).Address) 'Returns '$A$1'

End Sub

 

[stephicohu]

You can use .Address

Here is an example

Sub GetAddress()

    MsgBox (ActiveSheet.Cells(1, 1).Address) 'Returns '$A$1'

End Sub

Okay, I tried this and got the column address but when I put it into the Range(cells(cellstr, 3) it errors out and said the range failed.

 

[frabulator]

Okay, I tried this and got the column address but when I put it into the Range(cells(cellstr, 3) it errors out and said the range failed.

That is because you are using the function incorrectly.
When you use 'Cells' or 'Cell' you need to specify a row and then a column. I you have the address of what you want, then bypass the 'Cell' call.
Please see below:

Sub GetAddress()

    Dim Cel As String
    Cel = ActiveSheet.Cells(1, 1).Address
    ActiveSheet.Range(Cel).Select

End Sub

 

[MARK858]

@stephicohu Can clarify what you are trying to do please as the bits in red are looking for a number not a column letter or address

ActiveCell.FormulaR1C1 = "=SUM(RC[" & CStr(col1) & "]:RC[" & CStr(col2) & "])"

The code below will put the formula

=SUM(R3:W3)

in Sheets("January") cell AG3

Sub test()
    Dim col1 As Integer
    Dim cellnum As Integer
    Dim col2 As Integer


    cellnum = 33

    col1 = -15
    col2 = -10

    Sheets("January").Activate
    '
    '
    '
    Cells(3, cellnum).Select                     ' cell AG3

    ActiveCell.FormulaR1C1 = "=SUM(RC[" & col1 & "]:RC[" & col2 & "])"
End Sub

 

[stephicohu]

I am sorry but I was trying to be a brief as possible in my question and thus it diluted what I meant.

Situation

What I am doing, is I am making 12 worksheets that have the dates, sum of the week and the average of the week (up to 4 or 5 weeks) across Row 1. I am making a template for each month to be used during the month prescribe. These dates correspond to dates of the month where data is being enter day by day. The sum of the week and the average of the week (up to five) are in same position in each of the worksheets. The sum and the average are going to be formulas to give me the total sum and average per week. The given row and column number is known for each of the four to five weeks and are the same row and column for each month.

Problem I have

Instead of inserting the row and column number for each row, such as"AG3" for the formula for the sum and then "AH3" for the average for week 1 and then repeating this 4 to 5 times. I wanted to reduce the code length by making my own row and column address location. By knowing the row and column for each formula in each month, I wanted to do is make some kind of equation to satisfied the function: "Range (variable).Select".

What I was showing

This is just a test program so I can find out how to make it work. I want cell location "AG3" to be used, but without using the "AG3" in the "Range(variable).Select" function. The variable "Col1 and Col2" are just values to make up the sum function to work. The key to the problem is "AG3." I want to make code for that location. I thought what might work is the following:

1. create AG portion of the address
2. add the row 3 to the address portion by Cells(column, row)
3. convert to a string variable
4. then used that as my location variable.

I got an error using the above information. So, I was thinking how to make it work by replacing other items but it failed.

I hope by expanding what I am doing, this would help you understand. Again as I mentioned, I am doing a lot of self-taught googling and youtube research with only 3 weeks of programming in VBA coding. I have experience in other older software languages so I am very familiar on how codes work.

 

[MARK858]

I still don't see the advantage in converting something like Cells(3, 33).Select to Range(Cells(3, 33).Address).Select in what you are doing.
Cells(3, 33) is already a single cell range just as Range("AG3") is a single cell range and is more flexible.
It has no advantage converting it to a string when dealing with a single cell unless you specifically need it as a string (like in some formulas), it just makes Excel make an unnecessary evaluation/conversion and involves more typing.

Even in a multiple cell range you could do

Range(Cells(3, 33), Cells(3, 40)).Select

Having said that if you want to convert cells(3, 33) to AG3 and use it as a string variable

Sub test2()
    Dim NewStr As String
    NewStr = Cells(3, 33).Address(0, 0)
    Debug.Print NewStr
    Range(NewStr).Select
End Sub

or

Sub test3()
    Dim NewRng As Range, i As Integer, NewStr As String
    i = 3
    NewStr = "AG"
    Set NewRng = Range(NewStr & i)
    NewRng.Select
End Sub

whereas you just could create a range variable and just do

Sub test4()
    Dim NewRng As Range
    Set NewRng = Cells(3, 33)
    NewRng.Select
End Sub

or if you really wanted get a string variable from the range variable

Sub test5()

    Dim NewRng As Range

    Set NewRng = Cells(3, 33)

    NewRng.Select
  
    Debug.Print NewRng.Address(0, 0)

End Sub

The AG3 in Test1 and Test5 will appear in your Immediate window so you can see it has been converted

 

[stephicohu]

You know Mark858, I don't know. What I did was used the Excel 365 macros and the program's output to show the framework of what the program would look like. What I did do was use that as the bases of my code. If it said use the Range(variable).select as the way to select the cell location, I used it that in my solution. However, your solution works and is much easier to do, so I will be doing that in the future.

In one of the YouTube vids I watched said to start off your coding by using the Excel's macro function and then modify the code to satisfy your needs, which I have done.

Thank you for your help....

 

[MARK858]

You're welcome, the macro recorder is useful but remember all it does is record your actions and has it's defaults... Range is one of them.