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

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
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.

Code:
ws2.Range("A1").Value = ws1.Range("BL3").Value
Above snippet is from below here.
Code:
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
how about

Code:
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

New Member
Joined
Oct 11, 2019
Messages
42
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
It says
IF BL3 is not empty AND it contains a number THEN A1=BL3 ELSE A1 = BK3
 

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
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

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,732
Messages
5,597,802
Members
414,176
Latest member
LK88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top