0

In a program that I'm trying to write now I take two columns of numbers and perform calculations on them. I don't know where these two columns are located until the user tells me (they input the column value in a cell in the workbook that my code is located in).

For example, if the user inputted "A" and "B" as the columns where all the information is in I can perform calculations based on those values. Likewise if they wanted to analyze another worksheet (or workbook) and the columns are in "F" and "G" they could input those. The problem is that I'm asking the user to input those two columns as well as four others (the last four are the result columns). I did this in hopes that I would be able to make this flexible, but now inflexibility is acceptable.

My question is, if I'm given a value of where some information will be (let's say "F") how can I figure out what the column will be after or before that inputted value. So if I'm only given "F" I'll be able to create a variable to hold the "G" column.

Below are examples of how the variables worked before I needed to do this new problem:

Dim first_Column As String
Dim second_Column As String
Dim third_Column As String

first_Column = Range("B2").Text
second_Column = Range("B3").Text
third_Column = Range("B4").Text

Here the cells B2 - B4 are where the user inputs the values. Generally I want to be able to not have the B3 and B4 anymore. I feel like the Offset(0,1) might be able to help somehow but so far I've been unable to implement it correctly.

Thank you,

Jesse Smothermon

Improve this question

5 Answers 5

Reset to default
3

Here are two functions that will help you dealing with columns > "Z". They convert the textual form of a column to a column index (as a Long value) and vice versa:

Function ColTextToInt(ByVal col As String) As Long
    Dim c1 As String, c2 As String
    col = UCase(col) 'Make sure we are dealing with "A", not with "a"
    If Len(col) = 1 Then  'if "A" to "Z" is given, there is just one letter to decode
        ColTextToInt = Asc(col) - Asc("A") + 1
    ElseIf Len(col) = 2 Then
        c1 = Left(col, 1)  ' two letter columns: split to left and right letter
        c2 = Right(col, 1)
        ' calculate the column indexes from both letters  
        ColTextToInt = (Asc(c1) - Asc("A") + 1) * 26 + (Asc(c2) - Asc("A") + 1)
    Else
        ColTextToInt = 0
    End If
End Function

Function ColIntToText(ByVal col As Long) As String
    Dim i1 As Long, i2 As Long
    i1 = (col - 1) \ 26   ' col - 1 =i1*26+i2 : this calculates i1 and i2 from col 
    i2 = (col - 1) Mod 26
    ColIntToText = Chr(Asc("A") + i2)  ' if i1 is 0, this is the column from "A" to "Z"
    If i1 > 0 Then 'in this case, i1 represents the first letter of the two-letter columns
        ColIntToText = Chr(Asc("A") + i1 - 1) & ColIntToText ' add the first letter to the result
    End If
End Function

Now your problem can be solved easily, for example

newColumn = ColIntToText(ColTextToInt(oldColumn)+1)

EDITED accordingly to the remark of mwolfe02:

Of course, if you are not interested in the column names, but just want to get a range object of a specific cell in a given row right beneath a column given by the user, this code is "overkill". In this case, a simple

 Dim r as Range
 Dim row as long, oldColumn as String
 ' ... init row and oldColumn here ...

 Set r = mysheet.Range(oldColumn & row).Offset(0,1)
 ' now use r to manipulate the cell right to the original cell

will do it.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Am I missing something here? Is the asker trying to do something that can't be handled with the straightforward use of .Offset()?
@mwolfe02: he was asking for how to get to "G" when he knows "F". Or "AG" if he knows "AF". Perhaps a straightforward "Offset" will be enough to reach the right cell or column Range, but that would not give you the textual column name. But I agree with you, he should try to solve his problem using "Offset" first.
Ok.... you're obvious much better at coding than I am, haha. I have to admit that I'm having a hard time understanding the code. I'm not sure if you wanted to take the time to try and explain it to me, I've never been very good with following recursion by just looking at the code. Thank you for helping
I guess I have my thoughts about what a Function actually is mixed up. I'm a little more used to C# or Java. I assumed the line "ColTextToInt = Asc(col) - Asc("A") + 1" was sort of calling the Function again. But now that I know this isn't recursive I think I can figure it out. Thanks!
In VBA, the line "MyFunction = value" within a function "MyFunction" means the same as "return value;" in C#
|
2

You were on the right track with Offset. Here is a test function that shows a couple different approaches to take with it:

Sub test()

Dim first_Column As String
Dim second_Column As String
Dim third_Column As String
Dim r As Range

    first_Column = Range("B2").Text
    second_Column = Range("B2").Offset(1, 0).Text
    third_Column = Range("B2").Offset(2, 0).Text
    Debug.Print first_Column, second_Column, third_Column

    Set r = Range("B2")
    first_Column = r.Text
    Set r = r.Offset(1, 0)
    second_Column = r.Text
    Set r = r.Offset(1, 0)
    third_Column = r.Text
    Debug.Print first_Column, second_Column, third_Column

End Sub

UPDATE: After re-reading your question I realize you were trying to do offsets based on a user-entered column letter. @rskar's answer will shift the column letter, but it will be a lot easier to work with the column number in code. For example:

Sub test()
Dim first_Col As Integer, second_Col As Integer
    first_Col = Cells(, Range("B2").Text).Column
    second_Col = first_Col + 1

    Cells.Columns(first_Col).Font.Bold = True
    Cells.Columns(second_Col).Font.Italic = True
End Sub
Improve this answer

2 Comments

This does look a bit different from what I was trying to create when using Offset. The situation here is that I now only have one cell that the user can input a value into. Say the cell the user inputs into is "B2" but they input the value "F". Would the Offset return "C" or "G"? Sorry I'm frantically editing my code everywhere so I haven't tested this yet, but to me it seems like it would go to "C". That's at least what it did when I tried to write it without help, Thank you
I misunderstood your original question. When you said, "Generally I want to be able to not have the B3 and B4 anymore" I took that to mean that you were just trying to avoid hard-coding "B3" and "B4". I realize now that what you wanted was to not have the user enter a value for the second and third column.
2

There are a few syntactical problems with @rskar's answer. However, it was helpful in producing a function that grabs a column "letter", based on an input column "letter" and a desired offset to the right:

Public Function GetNextCol(TheCol As String, OffsetRight As Integer) As String
    Dim TempCol1 As String
    Dim TempCol2 As String
    TempCol1 = Range(TheCol & "1").Address
    TempCol2 = Range(TempCol1).Offset(0, OffsetRight).Address(0, 0, xlA1)
    GetNextCol = Left(TempCol2, Len(TempCol2) - 1)
End Function
Improve this answer

Comments

1

In light of the comments of others (and they all raised valid points), here is a much better solution to the problem, using Offset and Address:

Dim first_Column As String
Dim second_Column As String
Dim p As Integer

first_Column = Range("B2").Text

second_Column = _
    Range(first_Column + ":" + first_Column).Offset(0, 1).Address(0, 0, xlA1)
p = InStr(second_Column, ":")
second_Column = Left(second_Column, p - 1)

The above should work for any valid column name, "Z" and "AA" etc. included.

Improve this answer

Comments

0

Make use of the Asc() and Chr() functions in VBA, like so:

Dim first_Column As String
Dim second_Column As String

first_Column = Range("B2").Text
second_Column = Chr(Asc(first_Column) + 1)

The Asc(s) function returns the ASCII code (in integer, usually between 0 and 255) of the first character of a string "s".

The Chr(c) function returns a string containing the character which corresponds to the given code "c".

Upper case letters (A thru Z) are ASCII codes 65 thru 90. Just google ASCII for more detail.

NOTE: The above code will be fine so long as the first_Column is between "A" and "Y"; for columns "AA" etc., it will take a little more work, but Asc() and Chr() will still be the ticket to coding for that.

Improve this answer

7 Comments

I haven't heard of those..... I'm not too sure what the code is doing just by looking at it, sorry I'm still pretty new to VBA
Ok, I just wrote second_Column = Chr(Asc(first_Column)). If first_Column is "A" then this line makes second_Column also "A", likewise if first_Column is "F" then second_Column is also "F". I tried to do it quick and dirty and change it to second_Column = Chr(Asc(first_Column + 1)) but got a type mismatch. Since I don't know exactly what the user will input is there a way to get the number returned by Asc?.... and what does that question mark before Asc and Chr in your code do?
The code tells you that newcolumn = Chr(Asc(oldcolumn)+1) will give you what you are looking for as long as oldcolumn is in the range from "A" to "Y". Unfortunately this does not work for columns "Z", "AA", "AB" etc.
And the ? is a shortcut for Debug.Print.
@Jesse - Just rewrote the answer. welcome to VBA programming! Sorry about the question mark stuff, thought you already knew about the Immediate window. Hope this helps you get started.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.