index match match - VBA code loop ignore if cell contains data

M

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
222
Office Version
  1. 365
Platform
  1. Windows
Hi,

Apologies.. this might be a bit vague, I'm looking for suggestions if possible.

I will have a large matrix - may be 200 x 1000

To simplify the above, imagine I have a matrix of 3 x 3.

Across the top (as headers) are account numbers.
Down the left side are product codes.

On sheet 2, I have 3 columns
Column A - Product Codes
Column B - Account Numbers
Column C - Prices

This is what I would like to do:

Match the product code from Sheet 2, Cell A2 with the product code in Sheet 1, Column A (left of the matrix)
Match the account number from Sheet 2, Cell B2 with the account number in Sheet 1, Row 1 (the headers of the matrix)
Where they intersect in the Sheet 1 matrix, I want it to check if there is already a value in that cell.. if there is I want to highlight that cell in green and then move on to the next check, if there is no value, I would like to insert the corresponding price from Sheet 2, Cell C2

If I was doing it with a formula, I would use Index Match Match but that would overwrite any cells which already had a value in them... which is why I need to put it into code to check the cell first before inserting a value.

I would then want to loop onto the next one which would be match Cell's A3, B3 and insert C3 if there isn't a value already in the intersection cell.

Any suggestions for this please. Please bare in mind that this may end up being a huge matrix but it was easier to describe initially as a 3 x 3 matrix.

Thanks in advance for any suggestions.

Simon
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
MixedUpExcel said:
On sheet 2, I have 3 columns
Column A - Product Codes
Column B - Account Numbers
Column C - Prices
Click to expand...

I guess you have the following:

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:118.81px;" /><col style="width:131.17px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Product Codes</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Account Numbers</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Prices</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >cod1</td><td style="text-align:right; ">6001</td><td style="text-align:right; ">125</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >cod2</td><td style="text-align:right; ">6002</td><td style="text-align:right; ">215</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >cod3</td><td style="text-align:right; ">6003</td><td style="text-align:right; ">735</td></tr></table>

Sheet1 before execution:

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:201.5px;" /><col style="width:112.16px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; ">Product Codes / Account</td><td style="background-color:#ffff00; text-align:right; ">6001</td><td style="background-color:#ffff00; text-align:right; ">6002</td><td style="background-color:#ffff00; text-align:right; ">6003</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >cod1</td><td > </td><td style="text-align:right; ">50</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >cod2</td><td > </td><td style="text-align:right; ">100</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >cod3</td><td style="text-align:right; ">150</td><td > </td><td > </td></tr></table>



Sheet1 after execution:

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table> <table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:201.5px;" /><col style="width:112.16px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; ">Product Codes / Account</td><td style="background-color:#ffff00; text-align:right; ">6001</td><td style="background-color:#ffff00; text-align:right; ">6002</td><td style="background-color:#ffff00; text-align:right; ">6003</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >cod1</td><td style="text-align:right; ">125</td><td style="text-align:right; ">50</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >cod2</td><td > </td><td style="background-color:#00ff00; text-align:right; ">100</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >cod3</td><td style="text-align:right; ">150</td><td > </td><td style="text-align:right; ">735</td></tr></table>

Macro:

Code: 
Sub Complete_prices()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, f As Range, i As Long, j As Long
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
        Set f = sh1.Range("A:A").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            i = f.Row
            Set f = sh1.Rows(1).Find(c.Offset(0, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not f Is Nothing Then
                j = f.Column
                If sh1.Cells(i, j).Value = "" Then
                    sh1.Cells(i, j).Value = c.Offset(0, 2)
                Else
                    sh1.Cells(i, j).Interior.ColorIndex = 4
                End If
            End If
        End If
    Next
    MsgBox "End"
End Sub
 
Upvote 0
Hi DanteAmor,

Thank you for taking the time out to give me a working solution. You understood what I was looking to do and it works brilliantly.

As I wasn't sure if I'd explained it properly, I also put something together with a few additions I had hoped to do if I could get the start part working ok.

If the cell already contains a price, it checks the new price and if it matches, then it turns blue, red if it doesn't match and like yours, green if it's a new price and inserts the new price.

i'm going to look at how I can integrate yours with mine to make it more efficient.

This is my code:

Code: 
Sub price_loading()
Dim Prod As String, Acc As String, Price As String
Dim Price1 As String
Dim lnRow As Long, lnCol As Long
Dim i As Long
For i = 1 To 210
Prod = Sheets("New Prices").Cells(1, 1).Offset(i, 0).Value
Acc = Sheets("New Prices").Cells(1, 2).Offset(i, 0).Value
Price = Sheets("New Prices").Cells(1, 3).Offset(i, 0).Value
Price1 = Sheets("New Prices").Cells(1, 3).Offset(i, 0).Address
lnCol = Sheet1.Cells(1, 1).EntireRow.Find(What:=Acc, LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
lnRow = Sheet1.Cells(1, 1).EntireColumn.Find(What:=Prod, LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
If Len(Cells(lnRow, lnCol)) > 0 Then
    With Cells(lnRow, lnCol).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    With Sheets("New Prices").Range("" & Price1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
GoTo MOVEON
End If
Cells(lnRow, lnCol).Value = Price
    With Cells(lnRow, lnCol).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
MOVEON:
Next
End Sub

Thank you again.

Simon
 
Upvote 0
Ahh,
I've read the code i've posted.

It does everything apart from the check if the price is the same and change the cell blue.

I'd sent a copy from work to home but then made the extra changes (I'd forgotten weren't in the one I'd sent to home), so the final copy is on my works PC at the moment.

I'll try and remember to update the code with the final one tomorrow.

Thank you again though.

Simon
 
Upvote 0
MixedUpExcel said:
Hi DanteAmor,

Thank you for taking the time out to give me a working solution. You understood what I was looking to do and it works brilliantly.
Click to expand...

I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Hi,

Back in work.. here is the final code I had put together... then I'll look at incorporating yours. Thanks.

Code: 
Sub price_loading_checkmatchprice()

Dim Prod As String, Acc As Variant, Price As Variant
Dim Price1 As String
Dim lnRow As Long, lnCol As Long
Dim i As Long


For i = 1 To 210


Prod = Sheets("New Prices").Cells(1, 1).Offset(i, 0).Value
Acc = Sheets("New Prices").Cells(1, 2).Offset(i, 0).Value
Price = Sheets("New Prices").Cells(1, 3).Offset(i, 0).Value
Price1 = Sheets("New Prices").Cells(1, 3).Offset(i, 0).Address


lnCol = Sheet1.Cells(1, 1).EntireRow.Find(What:=Acc, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
lnRow = Sheet1.Cells(1, 1).EntireColumn.Find(What:=Prod, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row


If Len(Cells(lnRow, lnCol)) > 0 Then


    If Cells(lnRow, lnCol).Value <> Price Then
    
    With Cells(lnRow, lnCol).Interior ' red
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With


    With Sheets("New Prices").Range("" & Price1).Interior ' red
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
    ElseIf Cells(lnRow, lnCol).Value = Price Then
    
    With Cells(lnRow, lnCol).Interior ' blue
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
    End If
    
GoTo MOVEON
        
End If


Cells(lnRow, lnCol).Value = Price ' green


    With Cells(lnRow, lnCol).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With


MOVEON:


Next


End Sub
 
Upvote 0
MixedUpExcel said:
Hi,

Back in work.. here is the final code I had put together... then I'll look at incorporating yours. Thanks.

Code: 
Sub price_loading_checkmatchprice()
    Dim Prod As String, Acc As Variant, Price As Variant
    Dim Price1 As String
    Dim lnRow As Long, lnCol As Long
    Dim i As Long
    
    For i = 1 To 210
        Prod = Sheets("New Prices").Cells(1, 1).Offset(i, 0).Value
        Acc = Sheets("New Prices").Cells(1, 2).Offset(i, 0).Value
        Price = Sheets("New Prices").Cells(1, 3).Offset(i, 0).Value
        Price1 = Sheets("New Prices").Cells(1, 3).Offset(i, 0).Address
        
        lnCol = Sheet1.Cells(1, 1).EntireRow.Find(What:=Acc, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
        lnRow = Sheet1.Cells(1, 1).EntireColumn.Find(What:=Prod, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
        
        If Len(Cells(lnRow, lnCol)) > 0 Then
            If Cells(lnRow, lnCol).Value <> Price Then
                With Cells(lnRow, lnCol).Interior ' red
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.599993896298105
                    .PatternTintAndShade = 0
                End With
                With Sheets("New Prices").Range("" & Price1).Interior ' red
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.599993896298105
                    .PatternTintAndShade = 0
                End With
            ElseIf Cells(lnRow, lnCol).Value = Price Then
                With Cells(lnRow, lnCol).Interior ' blue
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent5
                    .TintAndShade = 0.599993896298105
                    .PatternTintAndShade = 0
                End With
            End If
        
[COLOR=#ff0000]        Else[/COLOR]
            
            Cells(lnRow, lnCol).Value = Price ' green
            With Cells(lnRow, lnCol).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            End With
        End If
    Next
End Sub
Click to expand...

You should not use the GoTo statement, the sequence of the program structure is lost. In this case, an Else must go.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Calculating from matrix cells
Replies
1
Views
130
Fluff
Fluff
L
  • Question Question
Table matrix value swapping
Replies
2
Views
330
Alex Blakenburg
A
S
  • Solved
IFS INDEX and MATCH based on Dropdown List
Replies
4
Views
373
bobsan42
bobsan42
U
  • Question Question
Dynamic Macro for INDEX/MATCH Formula
Replies
1
Views
365
StephenCrump
S
M
  • Question Question
Add "x" to a matrix
Replies
2
Views
201
Monikbor
M

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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