Macro problems (ElseIf)

aaronhowe

New Member
Joined
Oct 16, 2002
Messages
34
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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:

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

New Member
Joined
Oct 16, 2002
Messages
34
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
 

Forum statistics

Threads
1,143,914
Messages
5,721,501
Members
422,369
Latest member
redinator

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