JTL9161
Well-known Member
- Joined
- Aug 29, 2012
- Messages
- 567
- Office Version
- 365
- Platform
- Windows
I have a macro that works with a large spreadsheet (12,000+ rows)
It is a daily spreadsheet I update with previous day data. The macro pastes the updated info to the bottom of the existing data. Then the macro does a FIND of the record # in column A to locate that corresponding record in the spreadsheet. When it does it goes to column D and pastes the new data in. It then returns to the bottom of the spreadsheet and deletes the records it just worked on and if there are more new records it continues with the same process of pasting the new data.
What I found I need now is sometimes when the FIND finds the existing record and goes to column D to paste the updated info there could already be some data in that cell. Usually these cells are blank but sometimes there could already be data in that cell.
That is what I need to modify my macro to do. If the cell in column D for the found existing record already has data I need it to ignore pasting the updated data and return to bottom of the spreadsheet and delete the record just worked on the go on to the next one.
Here is the part of the macro that performs all of the commands above.
Do While ActiveCell <> ""
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
Selection.Copy
Set myActiveCell = ActiveCell
ActiveCell.Offset(0, -4).Select
With Worksheets(1).Range("a:a")
Dim value As String
value = ActiveCell.value
Dim c As Range
Set c = .Find(value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Activate
ActiveCell.Offset(0, 4).Select
ActiveSheet.Paste
myActiveCell.Activate
Rows(ActiveCell.Row).Delete
End If
End With
Loop
Again looking for modification that will ignore pasting new data in a (col D) cell if there is already data there. Only to paste if cell is blank.
Appreciate your help.
James
It is a daily spreadsheet I update with previous day data. The macro pastes the updated info to the bottom of the existing data. Then the macro does a FIND of the record # in column A to locate that corresponding record in the spreadsheet. When it does it goes to column D and pastes the new data in. It then returns to the bottom of the spreadsheet and deletes the records it just worked on and if there are more new records it continues with the same process of pasting the new data.
What I found I need now is sometimes when the FIND finds the existing record and goes to column D to paste the updated info there could already be some data in that cell. Usually these cells are blank but sometimes there could already be data in that cell.
That is what I need to modify my macro to do. If the cell in column D for the found existing record already has data I need it to ignore pasting the updated data and return to bottom of the spreadsheet and delete the record just worked on the go on to the next one.
Here is the part of the macro that performs all of the commands above.
Do While ActiveCell <> ""
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
Selection.Copy
Set myActiveCell = ActiveCell
ActiveCell.Offset(0, -4).Select
With Worksheets(1).Range("a:a")
Dim value As String
value = ActiveCell.value
Dim c As Range
Set c = .Find(value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Activate
ActiveCell.Offset(0, 4).Select
ActiveSheet.Paste
myActiveCell.Activate
Rows(ActiveCell.Row).Delete
End If
End With
Loop
Again looking for modification that will ignore pasting new data in a (col D) cell if there is already data there. Only to paste if cell is blank.
Appreciate your help.
James