Loop through column and update

sidharth84 · Jun 14th 2016

Hi Experts,
I have 2 sheets in a excel. Master and weeklyupdate. Master has 30 columns and weeklyupdate has 23 columns. The 23 columns in weeklyupdate sheet are in the sameorder as that of Master sheet. I have unique numbers in both the sheets in Column 1. For each cell in Column1 in Master sheet, I need to update the data ( 23 columns) from weeklyupdate sheet to the master sheet. Request your help.

skywriter · Jun 14th 2016

Re: Loop through column and update

A sample workbook would be helpful.

[sw]*[/sw]

sidharth84 · Jun 15th 2016

Re: Loop through column and update

Hi , Unfortunately my organization doesn't allow uploading . However, the data is as below.

Mastersheet :

PEC2011 John Smith 20 25 50 60 0 and so on till column X , then static data preloaded till AZ and not to be updated PEC1011 Kane 50 40 30 20 60 and so on till column X , then static data preloaded till AZ and not to be updated

Weeklyupdate :

PEC1011 Kane 50 30 10 20 10 and so on till column X
PEC2011 John Smith 10 25 50 10 10 and so on till column X

After compare and update , master sheet to look like ,

PEC2011 John Smith 10 25 50 10 10 and so onPEC1011 Kane 50 30 10 20 10 and so on

sidharth84 · Jun 18th 2016

Re: Loop through column and update

Hi, Managed to get the below code to do what i needed . But , instead of copying the entire row, how can i make this to copy only the first 23 columns ? Any help ?

Sub compareAndCopy()

Dim lastRowE As Integer
Dim lastRowF As Integer
Dim lastRowM As Integer
Dim foundTrue As Boolean

' stop screen from updating to speed things up
Application.ScreenUpdating = False
lastRowE = Sheets("Weeklyupdates").Cells(Sheets("Weeklyupdates").Rows.Count, "A").End(xlUp).Row
lastRowF = Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Row
For i = 1 To lastRowE
foundTrue = False
For j = 1 To lastRowF

If Sheets("Weeklyupdates").Cells(i, 1).Value = Sheets("Master").Cells(j, 1).Value Then
Sheets("Weeklyupdates").Rows(i).Copy Destination:= _
Sheets("Master").Rows(lastRowM + 1)
lastRowM = lastRowM + 1
Exit For
End If
Next j

Next i

' stop screen from updating to speed things up
Application.ScreenUpdating = True

End Sub

GShank · Jun 18th 2016

Re: Loop through column and update

Can you send the sample excel file to my mail id, so that i can check and revert?

sidharth84 · Jun 18th 2016

Re: Loop through column and update

Hi Experts,
PFA a sample workbook and a the code that I have done so far. While the sub updates the entire row, I need only columns that are highlighted in red to updated ( basically, the used columns for a specific cell from weeklyupdates sheet)

skywriter · Jun 18th 2016

Re: Loop through column and update

Try this. :cool:

Code

Sub compareAndCopy()
Dim rngData As Range, r As Range, rngFound As Range
' stop screen from updating to speed things up
Application.ScreenUpdating = False
With Worksheets("Weeklyupdates").Range("A1").CurrentRegion
    Set rngData = .Offset(1).Resize(.Rows.Count - 1, 1)
End With
With Worksheets("Master").Columns(1)
    For Each r In rngData
        Set rngFound = .Find(r.Value, LookAt:=xlWhole)
            If Not rngFound Is Nothing Then
                rngFound.Resize(, 7).Value = r.Resize(, 7).Value
            End If
    Next r
End With
' stop screen from updating to speed things up
Application.ScreenUpdating = True


End Sub

Display More

sidharth84 · Jun 18th 2016

Re: Loop through column and update

Simply Great ! I was just trying with resize from msdn and was surprised to see the same from you , Skywriter. Thanks a lot .

skywriter · Jun 18th 2016

Re: Loop through column and update

My pleasure, thanks for the feedback. :cool:

Loop through column and update

Participate now!

Share