| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with my code
Hi
Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help with my code
I think your issue is quotes - if you want to use quotes within a quoted
string, then you have to use two quotes, eg: "my string ""with quoted subsstring""" "kiwis" wrote: Hi Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help with my code
Hi Ben
i have tried your suggestion ..Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" / "",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)" it is still giving me error, application defined error or object defined error. Anyone can help? Thanks rgds kiwis On May 29, 3:23 pm, Ben McBen wrote: I think your issue is quotes - if you want to use quotes within a quoted string, then you have to use two quotes, eg: "my string ""with quoted subsstring""" "kiwis" wrote: Hi Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help with my code
Yes, because you have to construct the string correctly, concatenating the
part the parts that have to be evaluated. e.g. ".value" is not part of your string, as it has to be evaluated and THEN added to the string. ..Formula = "=IF(ISTEXT(" & .value & "), DATE(RIGHT(" & .value & ", 2),.... etc Do you have to do this with a formula in VBA ? There are easier ways of dealing with dates. e.g. DateSerial, DateValue, Format etc NickHK "kiwis" wrote in message oups.com... Hi Ben i have tried your suggestion .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" / "",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)" it is still giving me error, application defined error or object defined error. Anyone can help? Thanks rgds kiwis On May 29, 3:23 pm, Ben McBen wrote: I think your issue is quotes - if you want to use quotes within a quoted string, then you have to use two quotes, eg: "my string ""with quoted subsstring""" "kiwis" wrote: Hi Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help with my code
Sub AA()
Dim ws As Worksheet Dim ilastrow As Long Dim i As Long Set ws = ActiveSheet ilastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To ilastrow With ws.Cells(i, "B") .Offset(0, 1).Formula = "=IF(ISTEXT(" & _ .Address & "),DATE(RIGHT(" & _ .Address & ",2),MID(" & .Address & ",SEARCH(""/""," & _ .Address & ")+1,SEARCH(""/"", MID(" & .Address & _ ",SEARCH(""/""," & .Address & ")+1,10))-1),LEFT(" & _ .Address & ",SEARCH(""/""," & .Address & _ ")-1))," & .Address & ")" End With Next i End Sub produced the same formula you show in your example. (it places the formula in column C in the corresponding row. ) To do that, you need to use Address instead of Value. Plus the suggestions of the other posters. -- Regards, Tom Ogilvy' "kiwis" wrote: Hi Ben i have tried your suggestion ..Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" / "",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)" it is still giving me error, application defined error or object defined error. Anyone can help? Thanks rgds kiwis On May 29, 3:23 pm, Ben McBen wrote: I think your issue is quotes - if you want to use quotes within a quoted string, then you have to use two quotes, eg: "my string ""with quoted subsstring""" "kiwis" wrote: Hi Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help with my code
Thank you everyone for their reply,
NickHK - i have tried to set the format to what i want but to no sucess, so i have to write code to split up the date & then regroup into what i want mm/dd/yyyy. The orginal date was copied from a raw CSV file which is having dd/mm/yyyy format. Tom - thanks, the code work but the year is giving me 1903 instead of 2003. when i type the formula into the cell, i get the correct year 2003 but when i run the macro AA, the year becomes 1903, why is that so? any answer to why the year is wrong when using the marco but correct when i type into the cell? Thank you On May 29, 8:46 pm, Tom Ogilvy wrote: Sub AA() Dim ws As Worksheet Dim ilastrow As Long Dim i As Long Set ws = ActiveSheet ilastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To ilastrow With ws.Cells(i, "B") .Offset(0, 1).Formula = "=IF(ISTEXT(" & _ .Address & "),DATE(RIGHT(" & _ .Address & ",4),MID(" & .Address & ",SEARCH(""/""," & _ .Address & ")+1,SEARCH(""/"", MID(" & .Address & _ ",SEARCH(""/""," & .Address & ")+1,10))-1),LEFT(" & _ .Address & ",SEARCH(""/""," & .Address & _ ")-1))," & .Address & ")" End With Next i End Sub produced the same formula you show in your example. (it places the formula in column C in the corresponding row. ) To do that, you need to use Address instead of Value. Plus the suggestions of the other posters. -- Regards, Tom Ogilvy' "kiwis" wrote: Hi Ben i have tried your suggestion ..Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" / "",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)" it is still giving me error, application defined error or object defined error. Anyone can help? Thanks rgds kiwis On May 29, 3:23 pm, Ben McBen wrote: I think your issue is quotes - if you want to use quotes within a quoted string, then you have to use two quotes, eg: "my string ""with quoted subsstring""" "kiwis" wrote: Hi Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
need help with my code
i found the problem, the string "30/12/2005 " have a space which is
causing it to return year 1905 instead of 2005. solved the problem On May 30, 11:55 am, kiwis wrote: Thank you everyone for their reply, NickHK - i have tried to set the format to what i want but to no sucess, so i have to write code to split up the date & then regroup into what i want mm/dd/yyyy. The orginal date was copied from a raw CSV file which is having dd/mm/yyyy format. Tom - thanks, the code work but the year is giving me 1903 instead of 2003. when i type the formula into the cell, i get the correct year 2003 but when i run the macro AA, the year becomes 1903, why is that so? any answer to why the year is wrong when using the marco but correct when i type into the cell? Thank you On May 29, 8:46 pm, Tom Ogilvy wrote: Sub AA() Dim ws As Worksheet Dim ilastrow As Long Dim i As Long Set ws = ActiveSheet ilastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To ilastrow With ws.Cells(i, "B") .Offset(0, 1).Formula = "=IF(ISTEXT(" & _ .Address & "),DATE(RIGHT(" & _ .Address & ",4),MID(" & .Address & ",SEARCH(""/""," & _ .Address & ")+1,SEARCH(""/"", MID(" & .Address & _ ",SEARCH(""/""," & .Address & ")+1,10))-1),LEFT(" & _ .Address & ",SEARCH(""/""," & .Address & _ ")-1))," & .Address & ")" End With Next i End Sub produced the same formula you show in your example. (it places the formula in column C in the corresponding row. ) To do that, you need to use Address instead of Value. Plus the suggestions of the other posters. -- Regards, Tom Ogilvy' "kiwis" wrote: Hi Ben i have tried your suggestion ..Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" / "",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)" it is still giving me error, application defined error or object defined error. Anyone can help? Thanks rgds kiwis On May 29, 3:23 pm, Ben McBen wrote: I think your issue is quotes - if you want to use quotes within a quoted string, then you have to use two quotes, eg: "my string ""with quoted subsstring""" "kiwis" wrote: Hi Need some help with my code, My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy format. part of my code iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row For i = 2 To iLastRow With ws.Cells(i, "B") .Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value, SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" / ",.value)-1)),.value)" End With Next i i can do it in excel the formula = IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4, SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1, 10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4) but when i put it in a macro, i get error "Type mismatch" how do i set it such that it will loop through all the cells from in the for loop? Thank you I have send a few days trying to fix this date problem rgds kiwis- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
| Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
| Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
Hybrid Mode
