Help with "end if"

P

pm

I need to edit the following macro to include the date format of
"mm/dd/yyyy"...so I need to add another if statment below the If formatecode
= "mm/dd/yy"....I not sure where to place the "end if" statement....Can
anyone help?

'Copy format of data from Input Sheet to batch sheet
Sheets("Input").Select
Cells(r, c).Select
formatcode = Sheets("Input").Cells(r, c).NumberFormat
If formatcode = "mm/dd/yy" Then

Sheets(banksheet).Cells(bankrow, 5).NumberFormat = "@"
Sheets(banksheet).Cells(bankrow, 5) =
Format(Sheets("Input").Cells(r, c), "mmddyy")

Else
'If there is a formula in original cell, do it as
values/formats.
'If there is no formula in original cell,do it as All.
If Sheets("Input").Cells(r, c).HasFormula Then
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Else
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
End If
End If
'SAM Code
Sheets(banksheet).Cells(bankrow, 6) =
Sheets("Input").Cells(6, c)
End If
c = c + 1
Loop
End If
 
B

Bob Phillips

'Copy format of data from Input Sheet to batch sheet
Sheets("Input").Select
Cells(r, c).Select
formatcode = Sheets("Input").Cells(r, c).NumberFormat
If formatcode = "mm/dd/yy" Then

Sheets(banksheet).Cells(bankrow, 5).NumberFormat = "@"
Sheets(banksheet).Cells(bankrow, 5) =
Format(Sheets("Input").Cells(r, c), "mmddyy")

ElseIf formatcode = "mm/dd/yyyy" Then

'do your stuff

Else
'If there is a formula in original cell, do it as
values/formats.
'If there is no formula in original cell,do it as All.
If Sheets("Input").Cells(r, c).HasFormula Then
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Else
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
End If
End If
'SAM Code
Sheets(banksheet).Cells(bankrow, 6) =
Sheets("Input").Cells(6, c)
End If
c = c + 1
Loop
End If


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

Joel

I used an or statment instead

'Copy format of data from Input Sheet to batch sheet
Sheets("Input").Select
Cells(r, c).Select
formatcode = Sheets("Input").Cells(r, c).NumberFormat
If formatcode = "mm/dd/yy" or formatcode = "mm/dd/yyyy" Then

Sheets(banksheet).Cells(bankrow, 5).NumberFormat = "@"
Sheets(banksheet).Cells(bankrow, 5) =
Format(Sheets("Input").Cells(r, c), formatcode) 'put formatcode in this
statement

Else
'If there is a formula in original cell, do it as
values/formats.
'If there is no formula in original cell,do it as All.
If Sheets("Input").Cells(r, c).HasFormula Then
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Else
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
End If
End If
'SAM Code
Sheets(banksheet).Cells(bankrow, 6) =
Sheets("Input").Cells(6, c)
End If
c = c + 1
Loop
End If
 
P

pm

Thanks for your response Bob.

Bob Phillips said:
'Copy format of data from Input Sheet to batch sheet
Sheets("Input").Select
Cells(r, c).Select
formatcode = Sheets("Input").Cells(r, c).NumberFormat
If formatcode = "mm/dd/yy" Then

Sheets(banksheet).Cells(bankrow, 5).NumberFormat = "@"
Sheets(banksheet).Cells(bankrow, 5) =
Format(Sheets("Input").Cells(r, c), "mmddyy")

ElseIf formatcode = "mm/dd/yyyy" Then

'do your stuff

Else
'If there is a formula in original cell, do it as
values/formats.
'If there is no formula in original cell,do it as All.
If Sheets("Input").Cells(r, c).HasFormula Then
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Else
Selection.Copy
Sheets(banksheet).Select
Cells(bankrow, 5).Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
End If
End If
'SAM Code
Sheets(banksheet).Cells(bankrow, 6) =
Sheets("Input").Cells(6, c)
End If
c = c + 1
Loop
End If


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
Macro to run on hidden sheet 4
End Down a second time 1
How to macro to relative position 6
Macro to copy/paste from one workbook to another 8
macro or formula to paste values from one tab to another 1
VB macros issue with selecting cells on different page 1
Can I simplify a VB code?????? 3

Top