Boolean set first cell check if data then got to next cell

[tynawg]

Hello,
I would like to find the code to set a start cell to check if there is a number in it, if not check next cell in the row?
Example sometimes the source data is in BL3 and others its BK3. so if I can set a short loop it might solve an issue.

ws2.Range("A1").Value = ws1.Range("BL3").Value

Above snippet is from below here.

Sub Import_SOR_Data1()
'
' Import_SOR_Data Macro
    st = Timer
    Set ws1 = Sheets("ServiceOrder")
    Set ws2 = Sheets("Imported data")
    
    ws2.Range("A1").Value = ws1.Range("BL3").Value
    ws2.Range("A2").Value = ws1.Range("R16").Value
    
    ws1.Select
    lastrow = Cells(40, "A").End(xlDown).Row

    Range("a40:a" & lastrow).Copy
    ws2.Cells(3, "a").PasteSpecial
    
    Range("p40:p" & lastrow).Copy
    ws2.Cells(3, "b").PasteSpecial
    
    Range("ac40:ac" & lastrow).Copy
    ws2.Cells(3, "c").PasteSpecial
    
    Range("al40:al" & lastrow).Copy
    ws2.Cells(3, "d").PasteSpecial
    
    Range("bk40:bk" & lastrow).Copy
    ws2.Cells(3, "e").PasteSpecial
       
        
    For i = 1 To 5
        nr = Choose(i, 11, 11, 8, 55, 20)
        ws2.Columns(i).ColumnWidth = nr
    Next i
    
    ws2.Range("A3").CurrentRegion.Rows.AutoFit
    ws2.Select
    Cells(1, 1).Select
    Cells.Borders.LineStyle = xlLineStyleNone
    Debug.Print Timer - st                                            '0.18 sec
End Sub

Regards,
Wayne

 

[Yongle]

how about

Dim celA As Range, celB As Range, cel As Range
Set cel = ws2.Range("A1")
Set celA = ws1.Range("BL3")
Set celB = ws1.Range("BK3")

If IsNumeric(celA) And celA <> "" Then cel = celA Else cel = celB

 

[tynawg]

Wow, ok that makes my brain hurt.
I can see.....no i cant.....hmmmm..brainfreeze.
I think it says first check if its a number, then if not, clear, then check.....brain fart here haha

 

[Yongle]

It says
IF BL3 is not empty AND it contains a number THEN A1=BL3 ELSE A1 = BK3

 

[tynawg]

Hello,
Makes sense now, thank you very much for your help.
It throws a run time error 424 object required at Set cel = ws2.Range("A1")?

Regards,
Wayne

Sub Import_SOR_Data2()
'
' Import_SOR_Data Macro
    st = Timer
    Dim celA As Range, celB As Range, celC As Range
    Set cel = ws2.Range("A1")
    Set celA = ws1.Range("BL3")
    Set celB = ws1.Range("BK3")
    
    Set ws1 = Sheets("ServiceOrder")
    Set ws2 = Sheets("Imported data")
    
    'ws2.Range("A1").Value = ws1.Range("BL3").Value
    'rownr = Cells.Find("Service Order").Row
    'colnr = Cells.Find("Service Order").Column
    'Cells(rownr + 0, colnr).End(xlToRight).Select

    If IsNumeric(celA) And celA <> "" Then cel = celA Else cel = celB
    ws2.Range("A2").Value = ws1.Range("R16").Value
    
    ws1.Select
    lastrow = Cells(40, "A").End(xlDown).Row
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Trade").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period

    'Range("a40:a" & lastrow).Copy
    ws2.Cells(3, "a").PasteSpecial
    
    'Range("p40:p" & lastrow).Copy
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Item Code").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "b").PasteSpecial
    
    'Range("ac40:ac" & lastrow).Copy
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Qty/Hrs").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "c").PasteSpecial
    
    'ws2.Cells(3, "c").PasteSpecial
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Description").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "d").PasteSpecial
    'Range("al40:al" & lastrow).Copy
    
    'ws2.Cells(3, "d").PasteSpecial
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Location/Asset").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "e").PasteSpecial
    'Range("bk40:bk" & lastrow).Copy
    
    'ws2.Cells(3, "e").PasteSpecial
       
        
    For i = 1 To 5
        nr = Choose(i, 11, 11, 8, 55, 23)
        ws2.Columns(i).ColumnWidth = nr
    Next i
    
    ws2.Range("A3").CurrentRegion.Rows.AutoFit
    ws2.Select
    Cells(1, 1).Select
    Cells.Borders.LineStyle = xlLineStyleNone
    Debug.Print Timer - st                                            '0.18 sec
End Sub

 

[Yongle]

That is because ws1 and ws2 must be set before being used
- move the 2 lines setting the worksheets above Set cel = ws2.Range("A1")