#1
|
|||
|
|||
How to use "if" to copy and paste data
Hi all, this is my second post in this wonderful forum, I am dealing now with another weather data. The format I need is an hourly data for each month, but what I have is a 3-6 hours data, for example i have for the date 1/1/2005 the following hours:
00.00 06.00 09.00 . . . I need the data to be like this: 00.00 01.00 02.00 . . . 09.00 So what should be done is copy the data for the (00.00 hour) and paste them twice so I will have (00.00, 01.00 and 02.00) and they all have the same values (the value of the 00.00 hour). After that I have a missing data for hours 03.00, 04.00 and 05.00) so I have to create a row for each hour and fill each cell in those row with (9999). So I need an if statement that can see if the difference between (00.00) and (06.00) is 6 hours it will create (01.00, 02.00) from the (00.00 hour) then it will add rows for (03.00, 04.00 and 05.00) and give each cell in each row a value of (9999), and when the difference is 3 hours it will just copy and past the data of the previous hour, and in my example the difference between (06.00 and 09.00) it will only copy the data of the (06.00 hour) and paste them after it twice so it will create (07.00 and 08.00). Now another thing, I recorded macros to arrange the original data in the form I want, but since we have months with 30 days and others with 31 and others with 29 or 28 I couldn't add that for the macro, so is there is any way I can edit the macros to make them stop after they face an empty cell?? (Attached is an example for the data I want and a list for the macros I made so someone will help me edit them) The excel file I attached to this post contains three sheets: "original" which contains the original data, "my macros" which contain the data after I edited them using a recorded macro, and "final" which contain the data in their final format to use in the model and I highlighted the created rows in yellow, and I only put 10 in that sheet just to be brief, but when I will apply the macro I should make the whole days. And the "if" macro I will use it to the "my macros" sheet. Also you will find a list for the macros I recorded to produce the "my macros" sheet from the "original" sheet. Please guys, I have to finish this by the end of January in order to run the wind erosion model, and I am stuck in this issue only. Thank you all in advance Tareq |
#2
|
|||
|
|||
Hi,
some questions. Line 49 in "My macro" sheet, has a hour value of "1100". How should this be handled ? Do you get problems with months having 31 days or only the ones with less than 31 days? Can you attach an example how your macro run looks like, when it gives you this error. kind regards Bjorn |
#3
|
|||
|
|||
Quote:
About the other question, I made the macros for April and it works without any errors, so the macros should produce the wanted results for the months with 30 days, but if we deal with 31 days, then it will stop (i.e at the cell that April data ends) at the 30th day of that month, and if I was dealing with February, the data will work for extra rows. Actually I don't know how to attach the run of the macro, but you will see the recorded macros attached to a "txt" in my post. |
#4
|
|||
|
|||
Hi again,
as I see it there are two main issues. a) To make your macro work for 28-31 days b) to add the extra rows for the hours "inbetween". Regarding (a), it is not always easy to adjust an already recorded macro (much easier if it was "programmed"). One way of solving this is the following: 1) Re-record your macro for a month with 31 days (for example July). 2) Save this as your "permanent macro", that you in the future use for every month. 3) Run this macro for a short month (February, April, June etc). Please publish the result of this macro run (= how your excel sheet "my macro" looks like for april) here, so I can have a look at it. Then I can see if I can create an additional macro, that simply deletes the extra rows. Regarding (b) I am looking at it, but I am quite busy the next 2-3 days. Kind regards Bjorn |
#5
|
|||
|
|||
Quote:
|
#6
|
|||
|
|||
Hi,
try this! Activate the sheet "My Macros" and then run the macro below. This should turn this sheet into same appearance as the sheet "Final". Kind regards Bjorn Sub FixRows() Application.ScreenUpdating = False Dim rownum As Long Dim rowhour As Long rownum = 4 rowhour = 0 Do Until Cells(rownum, 4).Value = "" And rowhour = 0 If Cells(rownum, 4).Value <> rowhour And Cells(rownum, 4).Value Mod 100 = 0 Then Cells(rownum, 1).EntireRow.Insert Cells(rownum - 1, 1).Range("A1:L1").Copy _ Destination:=Cells(rownum, 1) Cells(rownum, 4).Value = rowhour If rowhour Mod 300 = 0 Then With Cells(rownum, 5) .NumberFormat = "0" .FormulaR1C1 = "9999" .Interior.ColorIndex = 6 .Interior.Pattern = xlSolid .Borders.LineStyle = xlcontinous .Borders.Weight = xlThin .Borders.ColorIndex = xlAutomatic End With Cells(rownum, 5).Copy Destination:=Cells(rownum, 1).Range("E1:L1") End If End If rownum = rownum + 1 rowhour = rowhour + 100 If rowhour = 2400 Then rowhour = 0 Loop Application.ScreenUpdating = False End Sub |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
Quote:
Your macro worked PERFECTLY!! Thank you very much. Regarding deleting extra rows, you will see an XLS file attached that includes the macro working for July 2006 (and I recorded the macro according to it) and June 2005. There will be 4 sheets, the first and the third sheets ("07_2006_BF" and (06_2005_BF") are the data before the macro while the second and the fourth sheets ("07_2006_AF" and "06_2005_AF") are the data after applying the macro. Also you will see a TXT file attached that includes the macro I recorded. Will you gonna edit the macros so they will stop working when they face an empty cell or you will make a new macro to delete the extra rows? And another thing, when I apply "macro2" a message appears to confirm pasting the cells over the existing cells and I should hit OK, how can I edit the macro so it will hit OK instead of me? Many thanks Bjorn for your GREAT effort with me, you really saved a lot of my time. Last edited by tareq; 01-26-2011 at 01:42 PM. |
#9
|
|||
|
|||
Hi,
try this around your macros: Sub macro xx() Application.DisplayAlerts = False (please your existing code here) Application.DisplayAlerts=True End sub You can also place these commands just around the part of the code that creates these kind of messages (how to find them? => Press Alt-F11 to see your macro, place cursor at macro beginning, run the macro "step by step" by pressing F8 over and over again, until you see what code creates this message). Kind regards Bjorn |
#10
|
|||
|
|||
Quote:
|
#11
|
|||
|
|||
Hi again,
after you have run your recorded macro's try this macro (still before the macro I wrote for you). It will delete rows where there is data in column "E" but no data in column "D". I think we are through now with all issues!! Kind regards Bjorn PS. Have a look at the code. As you see it is not so complicated with some simple macro's, why not try to learn? It started learning just some few months ago, just by googling around Sub FixShortMonth() Application.ScreenUpdating = False Dim rownum As Long rownum = 174 Do Until Cells(rownum, 5).Value = "" If Cells(rownum, 4).Value = "" Then Cells(rownum, 1).EntireRow.Delete Else rownum = rownum + 1 End If Loop Application.ScreenUpdating = False End Sub |
#12
|
|||
|
|||
Quote:
Man you really helped me! The fixmonth macro worked perfectly, and the code to fix the confirmation msg also worked ) I will learn about visual basic and macros, actually I learned a lot from you, but I am now racing the time, so what I do is observing what you do and then I will create something, I will deal later with weather data again but I will have time to learn and fix the issue ) Any ways, one last thing, when I applied your macro the "FixRows" macro it copied and pasted the header for the first 5 hours instead of just copying the data!! The result is attached in the sheet "06_2005_fixrow", I highlighted the error in red. If we you fixed this issue for me now I will finish the around 300 files by Sunday |
#13
|
|||
|
|||
Hi,
try this instead! Good night (at least in my part of the world :-) Kind regards Bjorn Sub FixRows() Application.ScreenUpdating = False Dim rownum As Long Dim rowhour As Long Dim CopyOffset As Long rownum = 4 rowhour = 0 Do Until Cells(rownum, 4).Value = "" And rowhour = 0 If Cells(rownum, 4).Value <> rowhour And Cells(rownum, 4).Value Mod 100 = 0 Then Cells(rownum, 1).EntireRow.Insert If rownum = 4 Then CopyOffset = 1 Else CopyOffset = -1 Cells(rownum + CopyOffset, 1).Range("A1:L1").Copy _ Destination:=Cells(rownum, 1) Cells(rownum, 4).Value = rowhour If rowhour Mod 300 = 0 Then With Cells(rownum, 5) .NumberFormat = "0" .FormulaR1C1 = "9999" .Interior.ColorIndex = 6 .Interior.Pattern = xlSolid .Borders.LineStyle = xlcontinous .Borders.Weight = xlThin .Borders.ColorIndex = xlAutomatic End With Cells(rownum, 5).Copy Destination:=Cells(rownum, 1).Range("E1:L1") End If End If rownum = rownum + 1 rowhour = rowhour + 100 If rowhour = 2400 Then rowhour = 0 Loop Application.ScreenUpdating = False End Sub |
#14
|
|||
|
|||
Quote:
Tareq. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Wierd symbols inplace of "space", "indentation" etc | aka.bhagvanji | Word | 5 | 02-16-2012 11:50 AM |
Replacing a single "l" with a double "ll" | MShroff | Word | 8 | 01-19-2011 08:43 AM |
"Microsoft Excel Application" missing in the "Component Services" on win08 | sword.fish | Excel | 0 | 02-26-2010 02:09 PM |
How do I change the "and" to "or" with multiple Rules (and Alerts)? | bschimmel | Outlook | 0 | 11-16-2009 05:26 AM |
Saving only "DATA" on excel? No white bottom? | jrasche2003@yahoo.com | Excel | 0 | 08-07-2006 09:27 AM |