Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-19-2011, 04:06 AM
tareq tareq is offline How to use "if" to copy and paste data Windows 7 How to use "if" to copy and paste data Office 2007
Novice
How to use "if" to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default 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
Attached Files
File Type: xls weatherdata.xls (162.0 KB, 14 views)
File Type: txt macros.txt (32.7 KB, 12 views)
Reply With Quote
  #2  
Old 01-19-2011, 03:11 PM
BjornS BjornS is offline How to use "if" to copy and paste data Windows Vista How to use "if" to copy and paste data Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 01-19-2011, 03:39 PM
tareq tareq is offline How to use "if" to copy and paste data Windows 7 How to use "if" to copy and paste data Office 2007
Novice
How to use "if" to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
Hi, thank you Bjorn for your prompt response. About the "1100" hour the macro should copy the "0900" hour and paste it once so it will create "1000" with the same data as "0900" and after that "1100" should be skipped and the macro should copy "1200" and paste it twice to create "1300" and "1400" with data the same as "1200".
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.
Reply With Quote
  #4  
Old 01-20-2011, 04:48 AM
BjornS BjornS is offline How to use "if" to copy and paste data Windows Vista How to use "if" to copy and paste data Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 01-20-2011, 08:24 AM
tareq tareq is offline How to use "if" to copy and paste data Windows 7 How to use "if" to copy and paste data Office 2007
Novice
How to use "if" to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
Hi, I will do the macro again and post it ASAP. Thank you )
Reply With Quote
  #6  
Old 01-21-2011, 02:18 PM
BjornS BjornS is offline How to use "if" to copy and paste data Windows Vista How to use "if" to copy and paste data Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 01-21-2011, 04:59 PM
tareq tareq is offline How to use &quot;if&quot; to copy and paste data Windows 7 How to use &quot;if&quot; to copy and paste data Office 2007
Novice
How to use &quot;if&quot; to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
WOW!! It worked!!! Thank you very much, I will try on a full data, I will try it for a year and I will let you know about the results, and tomorrow I will attach the re-recorded macro. Again, thank you very much BjornS ) you saved me )
Reply With Quote
  #8  
Old 01-26-2011, 03:21 AM
tareq tareq is offline How to use &quot;if&quot; to copy and paste data Windows 7 How to use &quot;if&quot; to copy and paste data Office 2007
Novice
How to use &quot;if&quot; to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
Hi Bjorn,
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.
Attached Files
File Type: xls Weather_Forum.xls (261.5 KB, 17 views)
File Type: txt macro_Forum.txt (11.3 KB, 12 views)

Last edited by tareq; 01-26-2011 at 01:42 PM.
Reply With Quote
  #9  
Old 01-26-2011, 01:27 PM
BjornS BjornS is offline How to use &quot;if&quot; to copy and paste data Windows Vista How to use &quot;if&quot; to copy and paste data Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 01-26-2011, 01:42 PM
tareq tareq is offline How to use &quot;if&quot; to copy and paste data Windows 7 How to use &quot;if&quot; to copy and paste data Office 2007
Novice
How to use &quot;if&quot; to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
I am very sorry!! It was "Macro2" that make the msg. I will try your solution now. Thanks )
Reply With Quote
  #11  
Old 01-26-2011, 01:46 PM
BjornS BjornS is offline How to use &quot;if&quot; to copy and paste data Windows Vista How to use &quot;if&quot; to copy and paste data Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 01-26-2011, 02:23 PM
tareq tareq is offline How to use &quot;if&quot; to copy and paste data Windows 7 How to use &quot;if&quot; to copy and paste data Office 2007
Novice
How to use &quot;if&quot; to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
Hey Bjorn!
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
Attached Files
File Type: xls Weather_Forum.xls (354.0 KB, 15 views)
Reply With Quote
  #13  
Old 01-26-2011, 03:10 PM
BjornS BjornS is offline How to use &quot;if&quot; to copy and paste data Windows Vista How to use &quot;if&quot; to copy and paste data Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 01-26-2011, 03:34 PM
tareq tareq is offline How to use &quot;if&quot; to copy and paste data Windows 7 How to use &quot;if&quot; to copy and paste data Office 2007
Novice
How to use &quot;if&quot; to copy and paste data
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
Thank you Bjorn, you fixed my problems COMPLETELY and I am very honored to contact with you, I wish we will still in contact I am very pleased to have a friend like you, I will send you my contact on a private message. It is also bed time in my world I live in Jordan, and it is now 12.33Am. Again, I highly appreciate your patience with me Best of luck my friend, and God bless you.
Tareq.
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to use &quot;if&quot; to copy and paste data 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft