Help with "end if"

[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

 

[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)

 

[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

 

[pm]

Thanks for your response Bob.

'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)