I have a question about merging several lists but I think it would be easier to explain if I can show an example. Someone suggested I post a sample on Dropbox. How would I post a link to the file?
Thanks
The three worksheet, Main, Update 1 and Update 2 have the same names in the same order. The letter in column A is a rating, R, G and Y. A want to add the rating from Update 1 in the Main sheet and then I want to add the ratings from Update 2 in the Main sheet.
When I add data to the main list, I want the data thats already there to remain there.
Sub UpdateMain()
' hiker95, 10/12/2017, ME1026657
Application.ScreenUpdating = False
Dim wm As Worksheet, w1 As Worksheet, w2 As Worksheet
Dim lr As Long, r As Range, a As String
Set wm = Sheets("Main")
Set w1 = Sheets("Update 1")
Set w2 = Sheets("Update 2")
With wm
lr = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
.Range("A1:A" & lr).ClearContents
For Each r In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
a = ""
If r.Offset(, -1) = vbEmpty Then
If w1.Range("A" & r.Row).Value <> "" Then
a = a & w1.Range("A" & r.Row).Value
End If
If w2.Range("A" & r.Row).Value <> "" Then
a = a & w2.Range("A" & r.Row).Value
End If
End If
If a <> "" Then
r.Offset(, -1).Value = a
End If
Next r
.Columns(1).AutoFit
End With
Application.ScreenUpdating = True
End Sub
There shouldn't be more than one rating per cell. Somebody suggested to use the ''&'' operator, sort of an "and" function for text. It would go this way:
In cell "A1" of the main sheet, the formula would be something like: =update1'A1&update2'A2&update3'A3 and so on.
The problem with that would be that it could put more than one value in a cell. I think that if I could put an ''or'' operator for text instead it could work. I don't know if that exist and what the syntax of it would be. It would go like this:
In cell "A1" of the main sheet, the formula would be something like: =update1'A1 "or" update2'A1 "or" update3'A1 and so on.
There shouldn't be more than one rating per cell.
Sub UpdateMain_V2()
' hiker95, 10/12/2017, ME1026657
Application.ScreenUpdating = False
Dim wm As Worksheet, w1 As Worksheet, w2 As Worksheet
Dim lr As Long, r As Range
Set wm = Sheets("Main")
Set w1 = Sheets("Update 1")
Set w2 = Sheets("Update 2")
With wm
lr = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
.Range("A1:A" & lr).ClearContents
For Each r In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
If r.Offset(, -1) = vbEmpty Then
If w1.Range("A" & r.Row).Value <> "" Then
r.Offset(, -1).Value = w1.Range("A" & r.Row).Value
End If
If w2.Range("A" & r.Row).Value <> "" Then
r.Offset(, -1).Value = w2.Range("A" & r.Row).Value
End If
End If
Next r
.Columns(1).AutoFit
End With
Application.ScreenUpdating = True
End Sub