VBA Compile Error

B

b13

New Member
Joined
Jul 25, 2011
Messages
46
Hi gurus,
I have this file which run a macros. But, i dont have an idea how it run/work. All i know is that it splits data then sort it. There are 3 steps or 3 required run of macros to be done. On the 3rd step a message pops up saying;

Compile error:
Block If without End If


What seems the problem?
Kindy help me with this matter.

Thanks a lot guys.;)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
It seems you are missing Endif after if statement.

Can you post your code?

Biz
 
Upvote 0
Hi biz,
tnx for replying
here are the codes
the debug is somewhere at the lower part
tnx a lot
sorry i dont know how to post codes



Application.ScreenUpdating = False


Dim StoreCo As String
Dim StoreSum As Double


Dim CounterA1 As Integer

Dim C1 As Integer

Dim D1 As Integer

Dim E1 As Integer

Dim Final1 As Integer

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim ThisFile As String
Dim tempLogFileName As String
Dim StoreTxtName As String
Dim strNetworkPrinter As String
Dim LoKaTion As String


Sheets(5).Select

Range("F7").Select
LoKaTion = ActiveCell.Value

Range("A1").Select

Sheets(1).Select
Range("AP2").Select
finalrow = Range("B9999").End(xlUp).Row
Range("A1:AY" & finalrow).Sort Key1:=Range("AP2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'----------------------------------------------------------------------------------

C1 = 2
Do While Final1 = 0
Sheets(3).Select
Range("A13:H10000").Select
Selection.Clear
CounterA1 = 0
Sheets(1).Select
D1 = 0
E1 = 16
Do While CounterA1 = 0 'loop1

Range("AP" & C1).Select



StoreCo = ActiveCell.Text
Sheets(1).Select 'Copy Text
Range("G" & C1).Select
Selection.Copy
Sheets(3).Select
Range("B13").Select
ActiveSheet.Paste
StoreTxtName = ActiveCell.Text


Sheets(1).Select 'Copy Vessel Code
Range("L" & C1).Select
Selection.Copy
Sheets(3).Select
Range("B8").Select
ActiveSheet.Paste

Sheets(1).Select 'Copy BatchName
Range("X" & C1).Select
Selection.Copy
Sheets(3).Select
Range("B" & E1).Select
ActiveSheet.Paste

Sheets(1).Select 'Copy InvoiceNumber
Range("W" & C1).Select
Selection.Copy
Sheets(3).Select
Range("C" & E1).Select
ActiveSheet.Paste

Sheets(1).Select 'Copy Supplier Code
Range("AC" & C1).Select
Selection.Copy
Sheets(3).Select
Range("D" & E1).Select
ActiveSheet.Paste

Sheets(1).Select 'Copy Supplier Name
Range("AD" & C1).Select
Selection.Copy
Sheets(3).Select
Range("E" & E1).Select
ActiveSheet.Paste

Sheets(1).Select 'Copy BCAmt
Range("H" & C1).Select
Selection.Copy
Sheets(3).Select
Range("G" & E1).Select
ActiveSheet.Paste


If D1 = 0 Then
Sheets(1).Select
C1 = C1 + 1
Range("AP" & C1).Select
D1 = 1
Else
Sheets(1).Select 'Del Entire Row
Rows(C1 & ":" & C1).Select
Selection.Delete Shift:=xlUp
Range("AP" & C1).Select

End If

E1 = E1 + 1
If ActiveCell.Value = StoreCo Then
CounterA1 = 0
Else
CounterA1 = 1
End If

Loop 'loop1

Sheets(3).Select

Range("G13").Select
ActiveCell.FormulaR1C1 = " USD"
Range("B15").Select
ActiveCell.FormulaR1C1 = "Ref No."
Range("C15").Select
ActiveCell.FormulaR1C1 = "Invoice No."
Range("D15").Select
ActiveCell.FormulaR1C1 = "Sup.Code"
Range("E15").Select
ActiveCell.FormulaR1C1 = "Sup.Name"
Range("G15").Select
ActiveCell.FormulaR1C1 = " Amount"

Range("C8").Select
Selection.FormulaR1C1 = "=VLOOKUP(RC[-1],'Ship code list'!R2C2:R65536C3,2,0)"

Range("B17").Select
If ActiveCell.Value = "" Then
Range("G19").Select
ActiveCell.FormulaR1C1 = "=R[-3]C"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
StoreSum = ActiveCell.Value
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total:"
ActiveCell.Offset(0, 4).Range("A1").Select

ActiveCell.Offset(-2, 0).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(2, 0).Range("A1").Select


Else
Range("G16").Select
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R16C7:R[-3]C7)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
StoreSum = ActiveCell.Value
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total:"
ActiveCell.Offset(0, 4).Range("A1").Select
End If

ActiveCell.Offset(-2, 0).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(2, 0).Range("A1").Select





Sheets(3).Select

strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
'ThisFile = "D:\My Documents\" & StoreTxtName & " " & StoreCo ' static file location
ThisFile = LoKaTion & StoreTxtName & " " & StoreCo ' variable file location
tempPSFileName = ThisFile & ".ps"
tempPDFFileName = ThisFile & ".pdf"
tempLogFileName = ThisFile & ".log"

ActivePrinter = strNetworkPrinter 'variable printer model
'ActivePrinter = "Adobe PDF on Ne05:" 'static printer model
ActiveWindow.SelectedSheets.PrintOut Copies:=1, PrintToFile:=True, PrToFileName:=tempPSFileName

Dim mypdfDist As New PdfDistiller

mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""

Kill tempPSFileName
Kill tempLogFileName

Sheets(1).Select 'Copy BatchName
Range("AZ" & C1 - 1).Select
ActiveCell.Value = ThisFile & ".pdf"
Range("H" & C1 - 1).Select
ActiveCell.Value = StoreSum
Range("K" & C1 - 1).Select
ActiveCell.Value = StoreSum

Range("W" & C1 - 1).Select
Selection.Clear
Range("X" & C1 - 1).Select
Selection.Clear

Range("AP" & C1).Select

If ActiveCell.Value = "" Then
Final1 = 1
End If

Loop

lro = Range("H9999").End(xlUp).Row + 1
Range("B" & lro).Select
ActiveCell.FormulaR1C1 = "'04"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "811017"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R2C6"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = StoreTxtName ''''''''''''''''''''''''''
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=-SUM(R2C8:R[-1]C8)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "USD"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=-SUM(R2C11:R[-1]C11)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 14).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R2C6"



' --- for year 2010 ---
If ActiveCell.Value = "20100201" Then
ActiveCell.FormulaR1C1 = "20100131"
ElseIf ActiveCell.Value = "20100301" Then
ActiveCell.FormulaR1C1 = "20100228"
ElseIf ActiveCell.Value = "20100401" Then
ActiveCell.FormulaR1C1 = "20100331"
ElseIf ActiveCell.Value = "20100501" Then
ActiveCell.FormulaR1C1 = "20100430"
ElseIf ActiveCell.Value = "20100601" Then
ActiveCell.FormulaR1C1 = "20100531"
ElseIf ActiveCell.Value = "20100701" Then
ActiveCell.FormulaR1C1 = "20100630"
ElseIf ActiveCell.Value = "20100801" Then
ActiveCell.FormulaR1C1 = "20100731"
ElseIf ActiveCell.Value = "20100901" Then
ActiveCell.FormulaR1C1 = "20100831"
ElseIf ActiveCell.Value = "20101001" Then
ActiveCell.FormulaR1C1 = "20100930"
ElseIf ActiveCell.Value = "20101101" Then
ActiveCell.FormulaR1C1 = "20101031"
ElseIf ActiveCell.Value = "20101201" Then
ActiveCell.FormulaR1C1 = "20101130"
ElseIf ActiveCell.Value = "20110101" Then
ActiveCell.FormulaR1C1 = "20101231"


' --- for year 2011 ---
If ActiveCell.Value = "20110201" Then
ActiveCell.FormulaR1C1 = "20100131"
ElseIf ActiveCell.Value = "20110301" Then
ActiveCell.FormulaR1C1 = "20110228"
ElseIf ActiveCell.Value = "20110401" Then
ActiveCell.FormulaR1C1 = "20110331"
ElseIf ActiveCell.Value = "20110501" Then
ActiveCell.FormulaR1C1 = "20110430"
ElseIf ActiveCell.Value = "20110601" Then
ActiveCell.FormulaR1C1 = "20110531"
ElseIf ActiveCell.Value = "20110701" Then
ActiveCell.FormulaR1C1 = "20110630"
ElseIf ActiveCell.Value = "20110801" Then
ActiveCell.FormulaR1C1 = "20110731"
ElseIf ActiveCell.Value = "20110901" Then
ActiveCell.FormulaR1C1 = "20110831"
ElseIf ActiveCell.Value = "20111001" Then
ActiveCell.FormulaR1C1 = "20110930"
ElseIf ActiveCell.Value = "20111101" Then
ActiveCell.FormulaR1C1 = "20111031"
ElseIf ActiveCell.Value = "20111201" Then
ActiveCell.FormulaR1C1 = "20111130"
ElseIf ActiveCell.Value = "20120101" Then
ActiveCell.FormulaR1C1 = "20111231"

' --- for year 2012 ---
If ActiveCell.Value = "20120201" Then
ActiveCell.FormulaR1C1 = "20110131"
ElseIf ActiveCell.Value = "20120301" Then
ActiveCell.FormulaR1C1 = "20120229"
ElseIf ActiveCell.Value = "20120401" Then
ActiveCell.FormulaR1C1 = "20120331"
ElseIf ActiveCell.Value = "20120501" Then
ActiveCell.FormulaR1C1 = "20120430"
ElseIf ActiveCell.Value = "20120601" Then
ActiveCell.FormulaR1C1 = "20120531"
ElseIf ActiveCell.Value = "20120701" Then
ActiveCell.FormulaR1C1 = "20120630"
ElseIf ActiveCell.Value = "20120801" Then
ActiveCell.FormulaR1C1 = "20120731"
ElseIf ActiveCell.Value = "20120901" Then
ActiveCell.FormulaR1C1 = "20120831"
ElseIf ActiveCell.Value = "20121001" Then
ActiveCell.FormulaR1C1 = "20120930"
ElseIf ActiveCell.Value = "20121101" Then
ActiveCell.FormulaR1C1 = "20121031"
ElseIf ActiveCell.Value = "20121201" Then
ActiveCell.FormulaR1C1 = "20121130"
ElseIf ActiveCell.Value = "20130101" Then
ActiveCell.FormulaR1C1 = "20121231"


End If


Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'ActiveCell.Value = StoreEntryDate
'ActiveCell.Value = "041253"

Columns("A:A").Select
Selection.ClearContents
Selection.Clear
Range("A1").Select
ActiveCell.FormulaR1C1 = "status"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("B:B").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Columns("C:C").Select
Selection.Clear
Range("C1").Select
ActiveCell.FormulaR1C1 = "voucher no."
Columns("D:D").Select
Selection.Clear
Range("D1").Select
ActiveCell.FormulaR1C1 = "Seq"
Columns("E:E").Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("E1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

Range("A2").Select
ActiveCell.FormulaR1C1 = "S"
Range("A" & lro).Select
ActiveCell.FormulaR1C1 = "E"

Columns("E:E").Select
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With

Columns("B:B").Select
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Columns("Y:Y").ColumnWidth = 9
Range("AC2:AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents




Application.ScreenUpdating = True

End Sub


Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
GetFullNetworkPrinterName = strTempPrinterName
i = 100 ' makes the loop end
End If
i = i + 1
Loop
Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer
End Function
 
Upvote 0
hi villy, tnx for the reply
I already put endif's on both ends.But still a run time error pops up.
And there is a yellow highlightened portion.
Particularly this code;

Kill tempPSFileName


tnx villy
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
511
picklefactory
P
L
  • Question Question
Compile error: Variable not defined
Replies
3
Views
276
lharr28
L
C
  • Solved
How to use conditional compiling in VBA
Replies
4
Views
462
Jon Peltier
J
V
  • Solved
Nested If Then ElseIF keeps returning Compile Error
2
Replies
10
Views
333
smozgur
smozgur
L
  • Question Question
Compile Error when Adding Attachments
Replies
5
Views
219
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,920
Latest member
jaspers

Share this page

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
Back
Top