Macro problems (ElseIf)

[aaronhowe]

I've pasted text below from my macro, which I am desperately trying to resolve as it's driving me mad...!

Can anyone see what the obvious flaw here is in the final If statements...? I'm getting errors all over the place about not blocking the If, no EndIf, etc etc. and it doesn't make sense!!!

Anyone?!
>>>>>>>>>>>>>

Sub CopyOutText()
Dim StrValue As String
'Will decide next mthod of action - move to AP or Montrose
StrValue = InputBox("Which sheet would you like to complete? (1 or 2)" & _
vbCrLf & vbTab & "1 - Hays Montrose/Logistics" & _
vbCrLf & vbTab & "2 - Hays AP/HPCC", "Sheet Method")
'Select copiable cells
Excel.Worksheets("Input sheet").Activate
Select Case StrValue
Case "1"
'1) Copies selected cells and pastes as Montrose
Range("B1").Select
Selection.Copy
'Opens correct workbook
Workbooks.Open ("C:WINNTIconsBackOfficePlusLog Montrose.xls")
'Activates that book and sheet
Worksheets("Pay").Activate
'Selects next cell down (1st blank row)
ActiveCell.Offset(1, 0).Select
'Pastes just the values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Activate original sheet
Excel.Workbooks("Rate Amend.xls").Activate
Range("B5").Select
Selection.Copy
'Open Target sheet again
Excel.Workbooks("Log Montrose.xls").Activate
'Selects next cell down (1st blank row)
ActiveCell.Offset(0, 1).Select
'Pastes just the values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Activate original sheet
Excel.Workbooks("Rate Amend.xls").Activate
Range("B6").Select
Selection.Copy
'Open Target sheet again
Excel.Workbooks("Log Montrose.xls").Activate
'Selects next cell down (1st blank row)
ActiveCell.Offset(0, 1).Select
'Pastes just the values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Case "2"
'2) Copies selected cells and pastes as AP
Range("B1").Select
Selection.Copy
'Opens correct workbook
Workbooks.Open ("C:WINNTIconsBackOfficePlusLog AP.xls")
'Activates that book and sheet
Worksheets("Pay").Activate
'Selects next cell down (1st blank row)
ActiveCell.Offset(1, 0).Select
'Pastes just the values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Activate original sheet
Excel.Workbooks("Rate Amend.xls").Activate
Range("B5").Select
Selection.Copy
'Open Target sheet again
Excel.Workbooks("Log AP.xls").Activate
'Selects next cell down (1st blank row)
ActiveCell.Offset(0, 1).Select
'Pastes just the values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Activate original sheet
Excel.Workbooks("Rate Amend.xls").Activate
Range("B6").Select
Selection.Copy
'Open Target sheet again
Excel.Workbooks("Log AP.xls").Activate
'Selects next cell down (1st blank row)
ActiveCell.Offset(0, 1).Select
'Pastes just the values
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Case Else
MsgBox "I don't think that was an option was it? Choose 1 or 2."
End
End Select
intLastRow = ActiveCell.Row
intLastColumn = ActiveCell.Column

'Now complete form
ActiveCell.Offset(0, 2).Range("A1").Activate
StrValue = InputBox("Handed to?", ActiveCell.Value)
ActiveCell.Value = StrValue
ActiveCell.Offset(0, 2).Range("A1").Activate
ActiveCell.Value = "Pay"
Excel.Workbooks("Rate Amend.xls").Activate

If Worksheets("Client").Range("H29").Value = _
"CONT" And Worksheets("Client2").Range("H29").Value < 0 Then _
Excel.Workbooks("Log Montrose.xls").Activate
ActiveCell.Offset(0, 3).Range("A1").Activate
ActiveCell.Value = "C"
ActiveCell.Offset(0, -6).Range("A1").Activate
ElseIf _
Worksheets("Client").Range("H29").Value < 0 Then _
Excel.Workbooks("Log Montrose.xls").Activate
ActiveCell.Offset(0, 3).Range("A1").Activate
ActiveCell.Value = "C"
ActiveCell.Offset(0, -6).Range("A1").Activate
Else _
ActiveWorkbook.Save
ActiveWorkbook.Close

 

[Von Pookie]

Well, if you use an if statement, at the end of it you need to put an EndIf. Essentially telling it that there is no more to that particular IF statement.

Other than saying that you need to put EndIf at the end of that, I can't really help.

 

[kskinne]

aaron,
it looks to me like you have your first IF statement and your THEN statement all on one line. this is not a block if statement, but a one line if statement, in which case you cannot have an elseif, an else, or an end if line or it will error out, because it is not a block if statement. to remedy this, change your code to a block if statement like this:

'an example of a block if statement
If [A1].value = 1 Then
selection.copy
ElseIf [A1].value = 2 Then
selection.offset(0,1).copy
Else
End
End If

notice how the command after the THEN appears on a new line.

hth
kevin
This message was edited by kskinne on 2002-10-22 12:29

 

[aaronhowe]

Showing how newbie I am to VB! I didn't realise that was what a block statement was... but it does make sense now. I'll let you know if it works :wink:

Cheers
Aaron