PDA

View Full Version : Get VBA to check if cells empty before autofill?



Immatoity
12-22-2005, 03:14 AM
Hiya

I have a basic macro or two that autofill a few sheets... however, when said sheets sometimes have no entries to autofill, it brings up an error message (obviously as it cant find anything to autofill)...

the code(extract) I am using is Range("b2").Select
Dim lastRowc As Long
lastRowc = Range("b65536").End(xlUp).Row
Range("a2:a2").Select
Selection.AutoFill Destination:=Range("a2:a" & lastRowc)

If there is no data apart from headers in row 1(which will always be there), I want the code to check this fact first and only run the above segment of code when necessary....

I know this is basic but we all started somewhere...:beerchug:

Bob Phillips
12-22-2005, 03:35 AM
Dim lastRowc As Long
lastRowc = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2")
If .Value <> "" Then
.AutoFill Destination:=.Resize(lastRowc - 1)
End If
End With

Immatoity
12-22-2005, 03:40 AM
sorted....cheers xld..works a treat

just in case anyone else is gonna use this..the line in code above

If .Value <> "" Then

should not include the . or you will get a mismatch error..

not moaning, just saving someone else mentioning it if they try it

Bob Phillips
12-22-2005, 05:33 AM
sorted....cheers xld..works a treat

just in case anyone else is gonna use this..the line in code above

If .Value <> "" Then

should not include the . or you will get a mismatch error..

not moaning, just saving someone else mentioning it if they try it

Yes it should, otherwise the Value will not refer to the Range object, it will juts refer to some implicit variable called Value, which would error if you had Option Explicit.

Immatoity
12-22-2005, 05:36 AM
hmmm very confused now as I cannot get code to work if I have the . in there??

here is the full code..it stops on the first ".Value" line and says type mismatch
ub Macro5LineNoandCopySheetsforSLDCrest()
Sheets("DDM1DC").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "LINE NO"
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(8, 1), Array(14, 9))
Range("B1").Select
ActiveCell.FormulaR1C1 = "CUST NO"
Range("A2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('Customer Master sheet'!C,MATCH(DDM1DC!RC[1],'Customer Master sheet'!C[1],0))"
Range("b2").Select
Application.ScreenUpdating = False
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2")
If .Value <> "" Then
.AutoFill Destination:=.Resize(lastRow - 1)
End If
End With
Sheets("CCM1DC").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "LINE NO"
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(8, 1), Array(14, 9))
Range("B1").Select
ActiveCell.FormulaR1C1 = "CUST NO"
Range("A2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('Customer Master sheet'!C,MATCH(CCM1DC!RC[1],'Customer Master sheet'!C[1],0))"
Range("b2").Select
Application.ScreenUpdating = False
Dim lastRowa As Long
lastRowa = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2")
If .Value <> "" Then
.AutoFill Destination:=.Resize(lastRowa - 1)
End If
End With
Sheets(Array("CCM1DC", "DDM1DC", "DDM3DC", "CCM3DC", "Data for Pivot DairyCrest")).Select
Sheets("Data for Pivot").Activate
Sheets(Array("CCM1DC", "DDM1DC", "DDM3DC", "CCM3DC", "Data for Pivot DairyCrest")).Copy
Sheets("Data for Pivot DairyCrest").Select
Selection.AutoFilter
Sheets("Data for Pivot DairyCrest").Select
Columns("I:W").Select
Selection.EntireColumn.Hidden = False
Sheets("Data for Pivot DairyCrest").Select
Columns("K:V").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("E2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom
Sheets("DDM1DC").Select
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("CCM1DC").Select
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Sheets("DDM3DC").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "LINE NO"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=INDEX(DDM1DC!C,MATCH(RC[2],DDM1DC!C[2],0))"
Range("b2").Select
Dim lastRowb As Long
lastRowb = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2")
If .Value <> "" Then
.AutoFill Destination:=.Resize(lastRowb - 1)
End If
End With
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("CCM3DC").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "LINE NO"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=INDEX(CCM1DC!C,MATCH(CCM3DC!RC[2],CCM1DC!C[2],0))"
Range("b2").Select
Dim lastRowc As Long
lastRowc = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2")
If .Value <> "" Then
.AutoFill Destination:=.Resize(lastRowc - 1)
End If
End With
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom





Windows("AR DD download for Hansa.xls").Activate
Sheets("Dairy Crest Macros").Select
End Sub



:dunno

Immatoity
12-23-2005, 03:28 AM
anyone help...ideally need to sort this by 2.00GMT as I leave!!

Bob Phillips
12-23-2005, 06:05 AM
anyone help...ideally need to sort this by 2.00GMT as I leave!!

You cannot reasonably expect us to test this without a sample workbook, it requires too much setup work on our part.

Immatoity
12-23-2005, 06:26 AM
xld...wasn't trying to be "off"

I cannot upload .xls or .zip files from my work pc as firewall kicks in, otherwise I would have done it...

I will try and send zip file home and upload from there..

cheers

Bob Phillips
12-23-2005, 11:20 AM
xld...wasn't trying to be "off"

I cannot upload .xls or .zip files from my work pc as firewall kicks in, otherwise I would have done it...

I will try and send zip file home and upload from there..

cheers

Wasn't suggesting that you were mate, just trying to explain why you may not have gotten a response.