Copy from sheet 1 to next free in sheet 2 (compare values)

**gangel** · 01-24-2012, 04:04 AM

Hi pplz,
Ive used "record macro" etc but im really sumpted on this one. Below is what I need to do!
(all these values are dates)

Looking at:
Range C3:C34 Sheet1 and comparing it to (a list of dates)
Range C3:C34 Sheet2

Compare Sheet1.C3 with Sheet2.C3,
if same, go to next row in the column (C4)
If DIFFERENT, paste Sheet1.C3 in next free cell on Sheet2 (eg into D3)
Then go to next cell
Continue for whole range

Therefore
C3->D3->E3->
C4->D4->E4

And so on.

I hope this makes sense! Im very new a VBa and trying to piece it all together but im getting confused with how to make one cell compare to another then move onto next. I know i can write every single range... just dont know how to write a small looping situation!

Cheers,

Glenn

**NickyC** · 01-24-2012, 04:45 AM

Hi Glenn
Not sure from your post where you want to insert the data on sheet 2 - this macro will insert it in the first blank cell to the right of columns C in sheet 2

Sub type_mismatches()
Dim RowNum As Variant, ColNum As Variant
For RowNum = 3 To 34 'specify rows to run the macro
    'find mismatch
    If Sheets(1).Columns(3).Rows(RowNum).Value <> Sheets(2).Columns(3).Rows(RowNum).Value Then
        ColNum = 4 ' starting in column d, find the first blank cell in row RowNum
        Do While Sheets(2).Columns(ColNum).Rows(RowNum).Value <> ""
            ColNum = ColNum + 1
        Loop
        Sheets(2).Columns(ColNum).Rows(RowNum).Value = Sheets(1).Columns(3).Rows(RowNum).Value
    End If
Next RowNum
End Sub

**smuzoen** · 01-24-2012, 05:03 AM

Unsure exactly if it is just 1 column you want to compare because you said C3-D3-E3??? However the following will compare 2 arrays and copy values that are different to next free column. Is there other data in the worksheet. Perhaps a sample workbook would help if this code is not what you need


Option Explicit
Sub compData()
On Error GoTo errHandler
   Dim orRng As Range, destRng As Range, a, b
   Dim k As Long, iCol As Long
   Set orRng = Worksheets("Sheet1").Range("C3:C34")
   Set destRng = Worksheets("Sheet2").Range("C3:C34")
 a = orRng
 b = destRng
 For k = LBound(a) To UBound(a)
 If a(k, 1) <> b(k, 1) Then
 iCol = Worksheets("Sheet2").Cells(k + 2, Columns.Count).End(xlToLeft).Column + 1
 Worksheets("Sheet2").Cells(k + 2, iCol) = a(k, 1)
 End If
 Next
 Exit Sub
errHandler:
 MsgBox "Error copying data - error " & Err.Number & " - " & _
 Err.Description
End Sub

This will also find last column with data and paste to the next cell in the row

**gangel** · 01-24-2012, 10:32 AM

ok ill send an example sheet tm.
Ive just noticed i havent explained well enough!
Thanks for the responses and check back in... 12 hrs?

**gangel** · 01-24-2012, 10:21 PM

Attached is the Workbook with both sheets as well as an explanation page with what I'm after.

I am aware taht I ahve changed the sheet names from 1 and 2!

THanks again!

**smuzoen** · 01-25-2012, 01:56 AM

I would use a worksheet_change event to update on the fly

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Dim dTaken As Date, wsTo As Worksheet, chkRng As Range
Dim k As Long, nFnd As Boolean, wsFrom As Worksheet
Set wsTo = Worksheets("Date Record")
Set wsFrom = Worksheets("New Webster Record Sheet")
If Target.Column = 7 Then
dTaken = Range("G" & Target.Row)
Set chkRng = wsTo.Range("C" & Target.Row).Resize(, wsTo.Cells(Target.Row, Columns.Count).End(xlToLeft).Column - 1)
a = chkRng
For k = LBound(a, 2) To (UBound(a, 2) - 1)
If a(1, k) = dTaken Then
nFnd = False
Exit For
Else
nFnd = True
End If
Next
If nFnd = True Then
wsTo.Cells(Target.Row, wsTo.Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1) = Format(dTaken, "dd/MMM")
End If
End If
Exit Sub
errHandler:
MsgBox "Error updating sheet - Check you have entered a valid date - error " & Err.Number & _
" - " & Err.Description
End Sub

As you add new values to column G on the Record Sheet it will check the Date Record sheet for the same value - if the same value is found no entry is made. If it is new then it is added to next available column. See attached workbook.

Hope this helps
Anthony

“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s)

**gangel** · 01-25-2012, 04:11 AM

OMG THANKYOU SO MUCH.
Works really well! Very very very happy, going to make my life so much easier at work!

Going to go throuhg the code now and dissect it to see what i was doing wrong!

I dont see where the actual "copy and paste' part is in that text though!

**smuzoen** · 01-25-2012, 10:09 AM

The section of code that copies the new date across is

wsTo.Cells(Target.Row, wsTo.Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1) = Format(dTaken, "dd/MMM")

Essentially I place the values in the Date Record sheet into an array by using a range and then I iterate through the array and see if the value in the Webster worksheet is present in the array. If it is not in the array then the variable nFnd is set to true and then this fires the code above.
It is better to do the copy in a Worksheet_Change event as the event occurs rather than when closing the workbook or using buttons otherwise you would need to check every row in the Webster worksheet against the Date Record sheet and this would take longer than just checking one row change when it occurs.
I hope that explains the code a little more for you. If you have any questions just ask.

Hope this helps
Anthony

“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s)

**gangel** · 02-10-2012, 12:36 AM

sounds great. Ive expanded on my requirements in this thread:
http://www.excelforum.com/excel-prog...19#post2701319
I started a new link as it is a differernt issue, but for the same workbook