Input Cell and select all rows under it

[cssfonseca]

Hello !

So i want to apply a macro to a column, but it may change the column from user to user.
So, i thought, if the user could indicate for what cell he wants to apply the macro, it would be easier.

But the problem is:
1. I have the macro i want to apply.
2. I have the button
3. i Don't know how i can "capture" the input cell and transform to the adress of the cell.

Like.. the user wants to apply the macro from k3 to the end of the column. I know i can user a input box to capture the value but i dont know how to apply in the macro itself.

Sub NoSpacesD()
Dim w As Range


For Each w In Range("D3:D1000")
w = Replace(w, " ", "")
w = Replace(w, ";", ".")
' see if ends in text, if not, msgbox error
Next
End Sub

but the column will be given by the user.
Can u help me?

 

[Joe4]

Here is a version which lets the user enter which column to run against, and does not use loops (loops are pretty inefficient, and you can save time and resources by avoiding them, if possible):

Sub NoSpacesD()

    Dim col As String
    Dim lr As Long
    Dim rng As Range

    Application.ScreenUpdating = False
    
'   Prompt user to enter the column to apply this to
    col = InputBox("Please enter the column letter you would like to apply this to")
    
'   Find last row in column
    lr = Cells(Rows.Count, col).End(xlUp).Row
    
'   Set rng to fix
    Set rng = Range(Cells(3, col), Cells(lr, col))

'   Replace values in range
    rng.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    rng.Replace What:=";", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Application.ScreenUpdating = True
    
End Sub

 

[cssfonseca]

Here is a version which lets the user enter which column to run against, and does not use loops (loops are pretty inefficient, and you can save time and resources by avoiding them, if possible):

Sub NoSpacesD()

    Dim col As String
    Dim lr As Long
    Dim rng As Range

    Application.ScreenUpdating = False
    
'   Prompt user to enter the column to apply this to
    col = InputBox("Please enter the column letter you would like to apply this to")
    
'   Find last row in column
    lr = Cells(Rows.Count, col).End(xlUp).Row
    
'   Set rng to fix
    Set rng = Range(Cells(3, col), Cells(lr, col))

'   Replace values in range
    rng.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    rng.Replace What:=";", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    Application.ScreenUpdating = True
    
End Sub

Thank you so much for your help !!
You saved my file !

Can i ask u just one thing?
When i run the macro and there is nothing to replace, it appears a msgbox saying there is nothing to replace. Can i hide it? Or make just appear once? Because my macro it supposed to run to over 10'000 products, so appearing that many msgbox is like killing myself, the user and the computer

 

[Joe4]

Odd, I do not get any message boxes saying that there is nothing to replace if there is nothing to replace.

In any event, see if making the changes in red suppresses them for you:

'   Replace values in range
    [COLOR=#ff0000]Application.DisplayAlerts = False[/COLOR]
    rng.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    rng.Replace What:=";", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    [COLOR=#ff0000]Application.DisplayAlerts = True[/COLOR]

 

[cssfonseca]

Solved the Problem ! Thank you !

Fyi, the message appeared when i had one of the cases to replace and not the other one like
25 25
25x25
Its running perfect now ! If i wanted to add more conditions, can i copy 3 lines of rng.replace without a problem? And if i want to check if the last char is text, can i do it my checking whats in rng atm?

 

[Joe4]

Its running perfect now ! If i wanted to add more conditions, can i copy 3 lines of rng.replace without a problem?

Yes.

And if i want to check if the last char is text, can i do it my checking whats in rng atm?

Not sure what you mean here. Are you checking the last entry, or the last character in every entry?
If checking the last character in each entry, you will probably need to use a loop in that instance.

 

[cssfonseca]

Yes.


Not sure what you mean here. Are you checking the last entry, or the last character in every entry?
If checking the last character in each entry, you will probably need to use a loop in that instance.

Yes, checking the last character. Too complex?

 

[Joe4]

Yes, checking the last character. Too complex?

No, not necessarily.

What exactly are the restrictions, and what do you want to happen when it finds something that runs afoul of the restrictions?

 

[cssfonseca]

No, not necessarily.

What exactly are the restrictions, and what do you want to happen when it finds something that runs afoul of the restrictions?

Sorry, i deleted the first part of my own answer.
If the last char of the cell is not text, then issues a msgbox saying that there is an error on cells A, B, C, etc. Else messages that everything its ok.

i'm using, what i think its a similar macro of what i want

Sub TamanhoNome()    Dim c As Range
    Dim Msg As String


    For Each c In Range("C1:C10000")
        If Len(c) > 80 Then Msg = Msg & vbTab & c.Address(RowAbsolute:=False, ColumnAbsolute:=False) & vbNewLine
    Next c


    If Len(Msg) > 0 Then MsgBox "Tamanho Excedido na Célula:" & vbNewLine & Msg
End Sub

But instead of checking the length, I guess I should use right?

 

[Joe4]

OK, we can make use of the ASCII code for each character in checking this.
The ASCII codes for each of the following are:
A: 65
Z: 90
a: 97
z: 122

So the upper case letters are ASCII codes 65-90, and the lower case letters are ASCII codes 97-122.
So we can identify any character that does not fall within these ranges, like this:

Sub MyTextCheck()

    Dim lr As Long
    Dim cell As Range
    Dim acode As Integer
    
'   Fix last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all cells in column C
    For Each cell In Range("C1:C" & lr)
'       See if any entry in cell
        If Len(cell) = 0 Then
            MsgBox "Missing entry in cell " & cell.Address(0, 0), vbOKOnly
        Else
'           Get ASCII character of last character in cell)
            acode = asc(Right(cell, 1))
'           Check to see if it is not a text entry
            If (acode < 65) Or (acode > 122) Or (acode > 90 And acode < 97) Then
                MsgBox "Last character not text in cell " & cell.Address(0, 0), vbOKOnly
            End If
        End If
    Next cell
        
End Sub

Note that I am checking for blank entries. If you do not care about those, simply comment out the MsgBox line pertaining to those.

Also, it is best to find the last entry in column C like I showed above instead of going through to row 10000. Loops are generally inefficient, so you really do not want to go any further than you have to.