a personal nightmare

[wigarth]

I have a working code to ask user for:
- a product
- what column to put it in
- quantity of product

All this happens based on customers region and checkboxes. (Checked regions will get data from user input)
Code is working fine in columns from C all the way to Z. But when I try to change the code to go all the way to "BU" it will always tell me that i have chosen a column not available to use.

Is there a way I can change "Z" to "BU" without making the macro fail?
here is my code: (from C to Z working fine...)

Sub MyPersonalNightmare()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet

    ' Variables to store values
    Dim Vareslag As String
    Dim Kolonne As String
    Dim Antall As String

    ' Spør brukeren om hva de skal lage manko på
    Vareslag = InputBox("Name on product", "Product Information", "Posters")

    ' Sjekk om brukeren har angitt tekst
    If Vareslag = "" Then
        MsgBox "No Product Information provided", vbExclamation, "Product Info"
        Exit Sub
    End If

Plassering:
    ' Ask user what column to place product in
    Kolonne = InputBox("Choose column (C to BU)", "Product Information")

    ' Validate that it is a valid column
    If Kolonne = "" Or (UCase(Kolonne) < "C" Or UCase(Kolonne) > "BU") Then
        MsgBox "Unavailable Column choosen, it needs to be between C and BU.", vbExclamation, "Product Information"
        GoTo Plassering
    End If

    ' Find column based on user choice
    Dim targetCol As Integer
    targetCol = Columns(Kolonne).Column

    ' check if column is in use already
    If Not IsEmpty(ws.Cells(9, targetCol)) Then
        Dim alternativKolonne As String
        alternativKolonne = InputBox("This Column is currently unavailable (Already in use) Choose another Column (C til BU):", "Product Information")
        
        ' Validate that new user defined column is valid
        If alternativKolonne = "" Or (UCase(alternativKolonne) < "C" Or UCase(alternativKolonne) > "BU") Then
            MsgBox "Unavailable Column chosen (Needs to be between C and BU.", vbExclamation, "Product Information"
            GoTo Plassering
        End If
        
        targetCol = Columns(alternativKolonne).Column
    End If

    ' Ask user of product quantity
    Antall = InputBox("How many units to chossen customers?", "Product Information", "1")

    ' Validate that user makes an input
    If Antall = "" Then
        MsgBox "Input is not valid, Exiting procedure", vbExclamation, "Product Information"
        Exit Sub
    End If

    ' Make headline for product in list
    ws.Cells(9, targetCol).Value = Vareslag

    ' Find quantity of product from user value
    Dim antallManglendeVarer As Integer
    antallManglendeVarer = CInt(Antall)

    ' Loop through list an put quantity on chosen customers in area BW12:BW308
    Dim cell As Range

    For Each cell In ws.Range("BW12:BW308")
        If cell.Value = "VISES" Then
            ' Find right column to put quantity in
            ws.Cells(cell.Row, targetCol).Value = antallManglendeVarer
        End If
    Next cell

    MsgBox "Routine completed succesfully", vbInformation
End Sub

 

[Dan_W]

The difficulty is that when you are checking if one string is < or > than another, it only checks the first character in the string, so the B in BU is lower than the C, thus causing the error.

Here is one possible solution (of many) - it is a function that takes three arguments - the target column (that you're having the user enter, and then a lower and upper bound (here, in your case, that would be C and BU). The function then tests to see whether the target column falls within the column range you've set and returns TRUE if yes, or FALSE if no:

Function ColumnIsInRange(ByVal Target As String, Optional ByVal LowerBound As String = "C", Optional UpperBound As String = "BU")
    ColumnIsInRange = Cells(1, Target).Column >= Cells(1, LowerBound).Column And Cells(1, Target).Column <= Cells(1, UpperBound).Column
End Function

The following code demonstrates how to use it, and shows the expected output results in the Immediate window

Sub TestRoutine()
    Debug.Print ColumnIsInRange("AA", "C", "BU")        ' Returns TRUE
    Debug.Print ColumnIsInRange("C", "C", "BU")         ' Returns TRUE
    Debug.Print ColumnIsInRange("BU", "C", "BU")        ' Returns TRUE
    Debug.Print ColumnIsInRange("A", "C", "BU")         ' Returns FALSE
    Debug.Print ColumnIsInRange("DA", "C", "BU")        ' Returns FALSE
End Sub

It may require a bit of adjustment to your code, but could make it a bit easier to work with?

 

[Legacy 143009]

If I were you I would change this line:

If Kolonne = "" Or (UCase(Kolonne) < "C" Or UCase(Kolonne) > "BU") Then

Like this:

If Kolonne = "" Or (Range(Kolonne & 1).Column < 3 Or Range(Kolonne & 1).Column > 73) Then

 

[Alex Blakenburg]

Which can also be expressed as:

If Kolonne = "" Or Columns(Kolonne).Column < 3 Or Columns(Kolonne).Column > 73 Then

 

[Alex Blakenburg]

Sadly I think you need a bit more error handing.
If you do the conversion in the If statement and they entered something that isn't a valid column letter(s) it will error out.
This should work

    Dim inputCol As Long
    On Error Resume Next
    inputCol = Columns(Kolonne).Column
    If Kolonne = "" Or inputCol < 3 Or inputCol > 73 Then
        MsgBox "Unavailable Column choosen, it needs to be between C and BU.", vbExclamation, "Product Information"
        On Error GoTo 0
        GoTo Plassering
    End If
    On Error GoTo 0