If cell is numeric only move to opposite cell

Question

I found a code which does exactly what I need but I'm unable to make it loop through sheet2 and sheet3?

Does anyone have a suggestion?

 Sub MoveMyNumbers()

    MoveNumbers "sheet1", "b"
    MoveNumbers "sheet2", "b"
End Sub



Function MoveNumbers(ShtName As String, ColLetter As String)
Dim ws As Excel.Worksheet
Dim ColNumber As Integer
Dim lRow As Long
Dim i As Long

Set ws = ThisWorkbook.Sheets(ShtName)
    lRow = ws.Range(ColLetter & ws.Rows.Count).End(xlUp).Row

   'Get Column Number from column letter
    ColNumber = ws.Range(ColLetter & "1").Column

    For i = 1 To lRow
        If IsNumeric(Cells(i, ColNumber).Value) Then
           Cells(i, (ColNumber + 1)).Value = Cells(i, ColNumber).Value
           Cells(i, ColNumber).Value = " "
           
        End If
    Next i
   End Function

The For loop is referencing unqualified `Cells(...)` and are therefore referencing the ActiveSheet. Prefix them with `ws.` — chris neilsen, Nov 27 '20 at 10:23

score 1 · Answer 1 · answered Nov 27 '20 at 08:43

Move Values to Adjacent Cell

It isn't working because you haven't qualified Cells in the For Next loop. Replace every occurrence of Cells in the For Next loop with ws.Cells or use the With statement:

A Rewrite

Option Explicit

Sub MoveMyNumbers()
    MoveNumbers "Sheet1", "B"
    MoveNumbers "Sheet2", "B"
    MoveNumbers "Sheet3", "B"
End Sub

Sub MoveNumbers(ByVal ShtName As String, ByVal ColLetter As String, _
    Optional ByVal ColOffset As Long = 1)
     
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets(ShtName)
    lRow = ws.Cells(ws.Rows.Count, ColLetter).End(xlUp).Row
    
    For i = 1 To lRow
        With ws.Cells(i, ColLetter)
            If IsNumeric(.Value) Then
                .Offset(, ColOffset).Value = .Value
                .Value = " " ' don't you rather want blank ""?
            End If
        End With
    Next i
    
End Sub

Each line in these line pairs does the same:

lRow = ws.Range(ColLetter & ws.Rows.Count).End(xlUp).Row
lRow = ws.Cells(ws.Rows.Count, ColLetter).End(xlUp).Row

ColNumber = ws.Range(ColLetter & "1").Column
ColNumber = ws.Columns(ColLetter).Column

Cells(i, (ColNumber + 1)).Value = Cells(i, ColNumber).Value
Cells(i, ColLetter).Offset(, 1).Value = Cells(i, ColNumber).Value

If cell is numeric only move to opposite cell

1 Answers1

Move Values to Adjacent Cell