Sheets

**visible4you** · 09-03-2011, 12:59 PM

Hi,

In attached Excel file, there are two sheets:-

1. Original
2. ImportedData

Imagine that Column J of "ImportedData" worksheet does not exist.

Based on Original worksheet, I want to update values in Column J of "ImportedData" worksheet through VBA:-

[VBA]
Private Sub Workbook_Open()

....

[/VBA]

I am more keen on using VBA, rather than to use nested IF and VLOOKUP. Please help me how can I do this?

**mikerickson** · 09-03-2011, 01:35 PM

I think this will do what you want

[VBA]Sub test()
Dim dataRange As Range
Dim critRange As Range
Dim resultRange As Range

With Sheet2.Columns(1)
Set dataRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Sheet1.Columns(1)
Set critRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With
Set resultRange = Sheet2.Columns(10).Resize(dataRange.Rows.Count, 1)

On Error Resume Next
dataRange.Parent.ShowAllData
resultRange.Value = "New"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=False
resultRange.SpecialCells(xlCellTypeVisible).Value = "duplicate"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=True
With resultRange.SpecialCells(xlCellTypeVisible)
.Value = "old"
.Cells(1, 1).Value = "Result header"
End With
dataRange.Parent.ShowAllData
On Error GoTo 0
End Sub[/VBA]

**visible4you** · 09-03-2011, 01:58 PM

Originally Posted by mikerickson

I think this will do what you want

[VBA]Sub test()
Dim dataRange As Range
Dim critRange As Range
Dim resultRange As Range

..........
End Sub[/VBA]

I had made following changes and I thank your reply: -

[VBA]
Dim dataRange As Range
Dim critRange As Range
Dim resultRange As Range

With Sheets(2).Columns(1)
Set dataRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Sheets(1).Columns(1)
Set critRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)

On Error Resume Next
dataRange.Parent.ShowAllData
resultRange.Value = "New"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=False
resultRange.SpecialCells(xlCellTypeVisible).Value = "duplicate"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=True
With resultRange.SpecialCells(xlCellTypeVisible)
.Value = "old"
.Cells(1, 1).Value = "Result header"
End With
dataRange.Parent.ShowAllData
On Error GoTo 0

[/VBA]

It worked without formatting. Program was acceptable

, but could you please rework on above program using For ... loop, if possible

?

**mikerickson** · 09-03-2011, 02:01 PM

could you please rework on above program using For ... loop

[vba]Dim dataRange As Range
Dim critRange As Range
Dim resultRange As Range
Dim i as Long

For i = 1 to 1
With Sheets(2).Columns(1)
Set dataRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Sheets(1).Columns(1)
Set critRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)

On Error Resume Next
dataRange.Parent.ShowAllData
resultRange.Value = "New"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=False
resultRange.SpecialCells(xlCellTypeVisible).Value = "duplicate"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=True
With resultRange.SpecialCells(xlCellTypeVisible)
.Value = "old"
.Cells(1, 1).Value = "Result header"
End With
dataRange.Parent.ShowAllData
On Error Goto 0
Next i[/vba]

**visible4you** · 09-03-2011, 02:13 PM

Excellent !

Lets Imagine that IF

(1) Sheet(2) don't have any data. Then an error may happen. In this case, We need to Display a specific Msgbox should ask to import data.

(2) Sheet(1) contains some rows which don't exist in Sheet(2), then those rows need to included in Sheet(2) and Col J to be marked as "Updated".

(3) Otherwise, then we need to add Rows marked as "New" to sheet(1) ?

**sukumar.vb** · 09-03-2011, 02:26 PM

I had been watching with Forum with great interest. I am interested to understand this step: -

[VBA]
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)
[/VBA]

Any specific reason for which this step was used?

From MSDN website, I got information that: -

Resizes the specified range. Returns a Range object that represents the resized range.

expression .Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be resized.

RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.

I am looking more to understand context in which it was used.

**mikerickson** · 09-03-2011, 02:38 PM

[VBA]Sub test2()
Dim dataRange As Range
Dim critRange As Range, crit2Range As Range
Dim resultRange As Range
Dim i As Long

For i = 1 to 1
With Sheet2.Columns(1)
Set dataRange = Range(.Cells(1, 9), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Sheet1.Columns(1)
Set critRange = Range(.Cells(1, 9), .Cells(.Rows.Count, 1).End(xlUp))
End With
Set resultRange = Sheet2.Columns(10).Resize(dataRange.Rows.Count, 1)

On Error Resume Next
dataRange.Parent.ShowAllData
resultRange.Value = "New"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=False
resultRange.SpecialCells(xlCellTypeVisible).Value = "duplicate"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=True
With resultRange.SpecialCells(xlCellTypeVisible)
.Value = "old"
.Cells(1, 1).Value = "Result header"
End With
dataRange.Parent.ShowAllData
On Error GoTo 0
With Range(dataRange, resultRange)
Set dataRange = .Cells
Set critRange = critRange.Resize(, .Columns.Count)
Set crit2Range = .Cells(1, .Columns.Count + 2).Resize(2, 1)
With crit2Range
.Cells(1, 1) = "Result header"
.Cells(2, 1) = "New"
End With
End With
With critRange
Set resultRange = .Offset(.Rows.Count, 0).Resize(1, .Columns.Count)
End With
dataRange.AdvancedFilter xlFilterCopy, crit2Range, resultRange
With resultRange
.Columns(.Columns.Count).EntireColumn.ClearContents
.Rows(1).Delete shift:=xlUp
End With
Crit2Range.ClearContents
Next i
End Sub[/VBA]

**visible4you** · 09-03-2011, 02:42 PM

Originally Posted by sukumar.vb

I had been watching with Forum with great interest. I am interested to understand this step: -

[vba]
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)
[/vba]
Any specific reason for which this step was used?

I am looking more to understand context in which it was used.

I understand that Resize was chosen to define result range based on datarange which was in turn based on Coulmn A to H (excluding col I) of Sheet(2) itself.

**visible4you** · 09-03-2011, 02:45 PM

Originally Posted by visible4you

Excellent !

Lets Imagine that IF

(1) Sheet(2) don't have any data. Then an error may happen. In this case, We need to Display a specific Msgbox should ask to import data.

(2) Sheet(1) contains some rows which don't exist in Sheet(2), then those rows need to included in Sheet(2) and Col J to be marked as "Updated".

(3) Otherwise, then we need to add Rows marked as "New" to sheet(1) ?

Hi mikerickson, your help had been awesome. I request to kindly review above conditions (1) and (2) too. Sorry, I updated it later, when you posted reply to condition (3) .

**mikerickson** · 09-03-2011, 02:47 PM

Originally Posted by sukumar.vb

I had been watching with Forum with great interest. I am interested to understand this step: -

[VBA]
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)
[/VBA]

Any specific reason for which this step was used?

From MSDN website, I got information that: -

Resizes the specified range. Returns a Range object that represents the resized range.

expression .Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be resized.

RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.

I am looking more to understand context in which it was used.

Each row in the sheet 2 data (dataRange) needed results.
Sheets(2).Columns(10) determined the column for those results
(dataRange.Offset(0,dataRange.Columns.Count).EntireColumn would generalize the same thing)
The Resize is then applied to give dataRange and resultRange the same number of rows.

**mikerickson** · 09-03-2011, 02:51 PM

"(1) If Sheet(2) don't have any data."

 If dataRange.Rows.Count = 1 Then MsgBox "Import Some Data":Exit Sub

"(2) Sheet(1) contains some rows which don't exist in Sheet(2)
(3) Otherwise, then we need to add Rows marked as "New" to sheet(1) ?"

Post #7 should address that.
With the updated sheet 1 being the ultimate output, there is no need for consideration #2, the 1-not-2 rows will still be on sheet 1.

**visible4you** · 09-03-2011, 03:05 PM

Awesome

How can your post # 7 also write "Updated" in Column J of Sheet(1) to answer condition 2.

**visible4you** · 09-03-2011, 03:18 PM

Originally Posted by mikerickson

"
"(2) Sheet(1) contains some rows which don't exist in Sheet(2)
(3) Otherwise, then we need to add Rows marked as "New" to sheet(1) ?"

Post #7 should address that.
With the updated sheet 1 being the ultimate output, there is no need for consideration #2, the 1-not-2 rows will still be on sheet 1.

I am not allowed to change entire contents of Sheet(1), unless to add a unique and new row from Sheet(2). But, I can comment on contents of Sheet(1) or Sheet (2) in Column J .

There is reason that, I will comment in Column J as Duplicate, Old, New or Updated only on basis of Col A, B and D

Please review and advice, if I have misunderstood.

**mikerickson** · 09-03-2011, 03:22 PM

[VBA]Sub test3()
Dim dataRange As Range
Dim critRange As Range, crit2Range As Range
Dim resultRange As Range
Dim i As Long

For i = 1 To 1
With Sheets(2).Columns(1)
Set dataRange = Range(.Cells(1, 9), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Sheets(1).Columns(1)
Set critRange = Range(.Cells(1, 9), .Cells(.Rows.Count, 1).End(xlUp))
End With
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)

On Error Resume Next
dataRange.Parent.ShowAllData
resultRange.Value = "New"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=False
resultRange.SpecialCells(xlCellTypeVisible).Value = "duplicate"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=True
With resultRange.SpecialCells(xlCellTypeVisible)
.Value = "old"
.Cells(1, 1).Value = "Result header"
End With
dataRange.Parent.ShowAllData
On Error GoTo 0
With Range(dataRange, resultRange)
Set dataRange = .Cells
Set critRange = critRange.Resize(, .Columns.Count)
Set crit2Range = .Cells(1, .Columns.Count + 2).Resize(2, 1)
With crit2Range
.Cells(1, 1) = "Result header"
.Cells(2, 1) = "New"
End With
End With
With critRange
Set resultRange = .Offset(.Rows.Count, 0).Resize(1, .Columns.Count)
End With
dataRange.AdvancedFilter xlFilterCopy, crit2Range, resultRange
resultRange.Rows(1).Delete shift:=xlUp
crit2Range.ClearContents
With critRange.Parent
Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).EntireRow.Columns(10).Value = "updated"
End With
Next i
End Sub[/VBA]

**mikerickson** · 09-03-2011, 03:23 PM

"I am not allowed to change entire contents of Sheet(1), unless to add a unique and new row from Sheet(2). But, I can only comment on contents of Sheet(1) or Sheet (2) "

This condition seems in conflict with the request:

"also write "Updated" in Column J of Sheet(1)"

**visible4you** · 09-03-2011, 03:37 PM

Originally Posted by mikerickson

"I am not allowed to change entire contents of Sheet(1), unless to add a unique and new row from Sheet(2). But, I can only comment on contents of Sheet(1) or Sheet (2) "

This condition seems in conflict with the request:

"also write "Updated" in Column J of Sheet(1)"

Sorry, I was not clear to express.

Here is revised: -

I am not allowed to change entire contents of Sheet(1), unless to add a unique and new row from Sheet(2). But, I can comment on contents of Sheet(1) or Sheet (2) in Column J .

There is reason that, I will comment in Column J as Duplicate, Old, New or Updated only on basis of Col A, B and D

The best way will be to swap Col C with Col D. And, then I can comment in Column J as Duplicate, Old, New or Updated only on basis of Col A, B and C.

**sukumar.vb** · 09-03-2011, 03:53 PM

Please help me understand, if I am correct with following Definitions:

Duplicate: Those duplicate values in Col A, B and D of Sheets(2)
Old: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).
New: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)
Updated: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).

So, can we call same row as "updated" in Sheets(1) , while we call it as "New" in Sheets(2)?

As understood from above, All aforesaid Definitions commented in Column J only.

**visible4you** · 09-03-2011, 04:08 PM

Originally Posted by sukumar.vb

Please help me understand, if I am correct with following Definitions:

Duplicate: Those duplicate values in Col A, B and D of Sheets(2)
Old: Those values which are same in Col A, B and D of Sheets(1) and Sheets(2).
New: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1)
Updated: Those values in Col A, B and D of Sheets(2) which does not exist in Sheets(1) and were updated in Sheets(1) using data from Sheets(2).

So, can we call same row as "updated" in Sheets(1) , while we call it as "New" in Sheets(2)?

As understood from above, All aforesaid Definitions commented in Column J only.

Yes, to some extent.

Attached: Those values in Col A, B and D of Sheets(1) which does not exist in Sheets(2) and were attached in Sheets(2) using data from Sheets(1).

**visible4you** · 09-03-2011, 05:19 PM

Working program up to here:-

[VBA]
Private Sub Workbook_Open()

Dim dataRange As Range
Dim critRange As Range
Dim resultRange As Range

With Sheets(2).Columns(1) 'ImportedData sheet used to set dataRange
Set dataRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With

If dataRange.Rows.Count = 1 Then

MsgBox "Import Some Data"
Exit Sub

Else

With Sheets(1).Columns(1) ' Original sheet used to set critRange
Set critRange = Range(.Cells(1, 8), .Cells(.Rows.Count, 1).End(xlUp))
End With

'defining range of J column using Rows count from A column
Set resultRange = Sheets(2).Columns(10).Resize(dataRange.Rows.Count, 1)

On Error Resume Next
dataRange.Parent.ShowAllData
resultRange.Value = "New" 'fills all rows in J Column as New

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=False
resultRange.SpecialCells(xlCellTypeVisible).Value = "duplicate"

dataRange.AdvancedFilter xlFilterInPlace, critRange, , unique:=True
With resultRange.SpecialCells(xlCellTypeVisible)
.Value = "old"
.Cells(1, 1).Value = "Result header"
End With
dataRange.Parent.ShowAllData
On Error GoTo 0

' From here *** missing program *** to include aforesaid Definitions.

' Following End IF is required / Please don't delete
End If
End Sub

[/VBA]

**sukumar.vb** · 09-04-2011, 01:55 PM

Hi,

I think that by making changes to following program posted above, we can have solution: -

[VBA]
With Range(dataRange, resultRange)
Set dataRange = .Cells
Set critRange = critRange.Resize(, .Columns.Count)
Set crit2Range = .Cells(1, .Columns.Count + 2).Resize(2, 1)
With crit2Range
.Cells(1, 1) = "Result header"
.Cells(2, 1) = "New"
End With
End With
With critRange
Set resultRange = .Offset(.Rows.Count, 0).Resize(1, .Columns.Count)
End With
dataRange.AdvancedFilter xlFilterCopy, crit2Range, resultRange
resultRange.Rows(1).Delete shift:=xlUp
crit2Range.ClearContents
With critRange.Parent
Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).EntireRow.Columns(10).Value = "updated"
End With
[/VBA]

Let me work on solution. Anyone is also welcome to post solution.

Thread: Sheets

Thread Tools

Display

Sheets

Posting Permissions