A Macro to Compare and copy two excel spreadsheets to 1 excel spreadsheet

U

ucsutah1

Board Regular
Joined
Jan 17, 2011
Messages
56
I am looking for a macro to compare two excel spreadsheets. On Column C on both spreadsheets it has a column name called ALU. I need the Macro to match ALU (data below the alu column) for each spreadsheet and if it matches to copy the cost and price over to to the 2nd spreadsheet based on the match of the ALU. The cost and price are on both spreadsheets with the same column names, column f (cost) and column g (price). The only other thing thats really important is to only copy across the cost/price if they aren't a blank or a 0.

Example of alu on each row - fan-venomx, kb-mk200, kb-gkg19
Example of Cost on each row - 53.00, 62.00, 176.00 and so on
Example of Price on each row - 92.35, 82.19, 203,12 and so on

i know this is kind of confusting, if you need more clarification or a sample of the spreadsheet please let me know?

thanks in advance
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
This should get you started:
Rich (BB code): 
Sub updateCurrentCostPrice()
    Dim r As Long, z As Long
    Dim curTable As Range
    Dim newTable As Range
    Dim found As Range
    Dim notFound As String
    
    Set curTable = Sheets("To").Range("C1:C" & Sheets("To").Cells(Rows.Count, 3).End(xlUp).Row)
    Set newTable = Sheets("From").Range("C1:C" & Sheets("From").Cells(Rows.Count, 3).End(xlUp).Row)
    
    For r = 1 To curTable.Rows.Count
        Set found = newTable.Find(curTable.Cells(r, 1), , -4163, 1, 2)
        If Not found Is Nothing Then
            If found.Offset(, 3)<> "" And found.Offset(, 3)<> 0 _
              And found.Offset(, 4)<> "" And found.Offset(, 4)<> 0 Then
                curTable.Cells(r, 1).Offset(, 3) = found.Offset(, 3)
                curTable.Cells(r, 1).Offset(, 4) = found.Offset(, 4)
            End If
        Else
            notFound = notFound & curTable.Cells(r, 1) & vbLf
        End If
    Next r
    
    If Len(notFound) > 1 Then MsgBox "Not Found:" & vbLf & notFound
    
End Sub
Excel Workbook
CDEFG
1aluCostPrice
2fan-venomx600
3kb-mk20094.51
4kb-gkg19202233.58
From
Excel 2007
Excel Workbook
CDEFG
1aluCostPrice
2fan-venomx5392.35
3kb-mk2006282.19
4kb-gkg19176203.12
To
Excel 2007
Excel Workbook
CDEFG
1aluCostPrice
2fan-venomx5392.35
3kb-mk2006282.19
4kb-gkg19202233.58
Result
Excel 2007
 
Upvote 0
thanks for the quick response, it works great except for one thing i am trying to figure out. when i ran the macro it copies everything over from 1 sheet to the other sheet and copying over blanks and 0s.

What i am trying to accomplish is compare sheet 1 & 2 through the alu and when sheet 2 has a 0 or blank and sheet 1 does not then copy cost & Price to sheet 2. Sheet 2=New Pricing, Sheet 1=Older Pricing.

Also one other thing how do i set it up with files instead of sheets ie (Inventory.xlsx, and Inventory1.xlsx)

The problem i have is i have 17000 products and when i do my pricing update i find a ton of products that don't have pricing on the new price sheet and are 0. Rather than 0 then i can have last weeks pricing at least. 0 normally means out of stock.

I hope this makes sense. If not i can clarify.

thanks again in advance
 
Upvote 0
The worksheets I posted are what I ran the code on and the result I got.

In my example:
My "From" sheet is your "New" sheet.
My "To" sheet is your "Older" sheet.
My "Result" sheet is my "To" sheet after running the code

So after running, only Row 4 on the "TO" sheet updated because
"From" G2 is Zero and "From" F3 is Blank

Isn't this right??

If yours copied over Zeros and Blanks then...
Is your data in the same cols & starting at row1 as in my post?
If you changed sheetnames in the code, did you get them all?

Can you post sample data worksheet?
download this addin: http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

We can deal with diff files when we get this right.
What version Excel do you have?
 
Last edited:
Upvote 0
i am sorry i have know idea why it reposts the same thing i put posted earlier. There shouldn't have been a repost. I just logged and logged back in and did a back browser and it showed a double post. I will be sending some examples and retesting on your code as soon as i can. I will try to delete it.

thanks
 
Upvote 0
old pricing

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>alu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>D266SC512H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>W1066UA1GS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>DR266SO512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10</TD></TR></TBODY></TABLE>

new pricing

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>alu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>D266SC512H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>W1066UA1GS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>DR266SO512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>30</TD></TR></TBODY></TABLE>

result

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>alu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>D266SC512H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>W1066UA1GS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>DR266SO512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10</TD></TR></TBODY></TABLE>

On the result d266sc512h and w1066ua1gs are correct, however dr266so512 is incorrect mainly because new pricing already had a price on it and wasn't 0 or a blank. Does it make sense now? Thanks

Desired result should be

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>alu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>D266SC512H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>W1066UA1GS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 colSpan=2>DR266SO512</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>30</TD></TR></TBODY></TABLE>
 
Upvote 0
Warship said:
The worksheets I posted are what I ran the code on and the result I got.

In my example:
My "From" sheet is your "New" sheet.
My "To" sheet is your "Older" sheet.
My "Result" sheet is my "To" sheet after running the code

So after running, only Row 4 on the "TO" sheet updated because
"From" G2 is Zero and "From" F3 is Blank

Isn't this right??
Click to expand...
Warship said:
This should get you started:
Excel Workbook
CDEFG
1aluCostPrice
2fan-venomx600
3kb-mk20094.51
4kb-gkg19202233.58
From
Excel 2007
Excel Workbook
CDEFG
1aluCostPrice
2fan-venomx5392.35
3kb-mk2006282.19
4kb-gkg19176203.12
To
Excel 2007
Excel Workbook
CDEFG
1aluCostPrice
2fan-venomx5392.35
3kb-mk2006282.19
4kb-gkg19202233.58
Result
Excel 2007
Click to expand...


It still seems that our results are the same, different values, but same principle.

Do you get your results with the code?
 
Upvote 0
Sorry, i finally had a chance to work on this again. Thanks for your patience.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
on your example i got exactly what you got, however you stated "So after running, only Row 4 on the "TO" sheet updated because
From G2 is Zero and "From" F3 is Blank"
<o:p></o:p>
<o:p></o:p>
I appologize if there was a confusion. i am actually trying to do the opposite. I will try to explain this the best i can with examples.<o:p></o:p>
<o:p></o:p>

Should be<o:p></o:p>
<o:p></o:p>

From=Old Pricing File and To=New Pricing File<o:p></o:p>
<o:p></o:p>

The only pricing sheet that needs to change is the new pricing sheet, but only if the new pricing sheet has a 0 or blank, if it doesn't then it needs to be left the same. If it does and the old pricing sheet has a number above 0 for the same alu then change that 0 or blank from the old pricing sheet to the new pricing sheet. Example<o:p></o:p>
<o:p></o:p>

Old Pricing<o:p></o:p>
<o:p></o:p>

C F G<o:p></o:p>
Alu Cost Price <o:p></o:p>
fan-venomx 53 92.35<o:p></o:p>
kb-mk 200 0 0<o:p></o:p>
kb-gkg19 202 233.58<o:p></o:p>
<o:p></o:p>

New Pricing<o:p></o:p>
<o:p></o:p>

C F G<o:p></o:p>
Alu Cost Price <o:p></o:p>
fan-venomx 0 0<o:p></o:p>
kb-mk200 75.00 89.00<o:p></o:p>
kb-gkg19 409.00 603.00<o:p></o:p>
<o:p></o:p>

Result<o:p></o:p>
<o:p></o:p>

C F G<o:p></o:p>
Alu Cost Price <o:p></o:p>
fan-venomx 52 92.35<o:p></o:p>
kb-mk200 75.00 89.00<o:p></o:p>
kb-gkg19 409.00 603.00
<o:p></o:p>
<o:p></o:p>
On the result old pricng f2 & g2 replace new pricing f2 & G2 because they were 0's. On the result old pricing f3 & G3 were 0's so it didn't replace f3 & g3 pricing on new pricing and new pricing already had a price above 0 so it stayed the same and no change. On the result old pricing f4 & g3 because new pricing already had pricing above 0 the pricing stayed the same even though there were different prices from the old pricing. <o:p></o:p>
<o:p></o:p>
So the bottom line is if there is a 0 or blank on new pricing and the old pricing has a price above 0 then change it. If old pricing has a price and new pricing has a price don't change it because new pricing is above 0 already. (blank is = 0, and new pricing is all that need to be changed)<o:p></o:p>
<o:p></o:p>

The main issue i see in yours is its updating row 4 of values 202.00 and 233.58 when it should stay the same value and not update.<o:p></o:p>
<o:p></o:p>
So what i see is on your macro it is updating the pricing when there is already a price. from old to new. try more than 3 and you will see what i mean. It old & new have already a price it shouldn't update at all. It should just update only when there is a 0 or blank.<o:p></o:p>
<o:p></o:p>

I hope this finally makes sense.<o:p></o:p>
<o:p></o:p>

thanks for your help and patience. <o:p></o:p>
 
Last edited:
Upvote 0
Rich (BB code): 
Sub updateNewPriceList()
    Dim r As Long, z As Long
    Dim newTable As Range
    Dim oldTable As Range
    Dim found As Range
    Dim notFound As String
    Set oldTable = Sheets("OLD").Range("C1:C" & Sheets("OLD").Cells(Rows.Count, 3).End(xlUp).Row)
    Set newTable = Sheets("NEW").Range("C1:C" & Sheets("NEW").Cells(Rows.Count, 3).End(xlUp).Row)
    For r = 1 To newTable.Rows.Count
        If (newTable.Cells(r, 1).Offset(, 3) = "" Or newTable.Cells(r, 1).Offset(, 3) = 0) _
          And (newTable.Cells(r, 1).Offset(, 4) = "" Or newTable.Cells(r, 1).Offset(, 4) = 0) Then
            Set found = oldTable.Find(newTable.Cells(r, 1), , -4163, 1, 2)
            If Not found Is Nothing Then
                newTable.Cells(r, 1).Offset(, 3) = found.Offset(, 3)
                newTable.Cells(r, 1).Offset(, 4) = found.Offset(, 4)
            Else
                notFound = notFound & newTable.Cells(r, 1) & vbLf
            End If
        End If
    Next r
    If Len(notFound) > 1 Then MsgBox "Not Found:" & vbLf & notFound
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
419
STARL0RD
S
J
  • Question Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
342
JMC57
J
S
  • Question Question
Compare several columns on two different sheets and copy the differences to another sheet
Replies
5
Views
428
sofas
S
M
  • Question Question
My formula is unnecessarily copied to the adjacent cell when entering data into another cell
Replies
6
Views
331
MrsGrayMarlin
M
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
982
ahaye
A

Forum statistics

Threads
1,224,616
Messages
6,179,912
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top