| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with "end if"
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with "end if"
'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) "pm" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with "end if"
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with "end if"
Thanks for your response Bob.
"Bob Phillips" wrote: '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) "pm" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
