Using Arrays instead of this code

A

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
Hi,
I have never used arrays before and what I've read either scares or confuses me.

What I need to know is would the use of an array be faster and/or easier than the code below (Bit in question is between XXXX's)

This is just a very simple example to illustrate what is a far more complex issue I need to solve. If I can see how this simple examplwe would work I could then apply it to my real problem.

How would the following code translate into an effective array?


Code: 
Sub Test2()
    Dim vProductCode As String
    Dim vTaxYN As String
    Dim vPrice As Double
    Dim wsProd As Worksheet
    Dim wsInv As Worksheet
    Dim FinalRowProd As Long
    Dim FinalRowInv As Long
 
    Set wsProd = Worksheets("Product")
    Set wsInv = Worksheets("Invoice")
 
    FinalRowProd = wsProd.Cells(Rows.Count, 1).End(xlUp).Row
    FinalRowInv = wsInv.Cells(Rows.Count, 1).End(xlUp).Row
    'XXXXXXXXXXX
        For i = 1 To FinalRowInv
        vProductCode = wsInv.Cells(i, 1)
            For j = 1 To FinalRowProd
                If vProductCode = wsInv.Cells(j, 1) Then
                    wsInv.Cells(i, 2) = wsProd.Cells(j, 2)
                    wsInv.Cells(i, 3) = wsProd.Cells(j, 3)
                End If
            Next j
        Next i
    'XXXXXXXXXXX
End Sub

There are just 2 worksheets in this example. "Product" - in col A is product code, Col B a "Y" or "N" and Col C a value.
In sheet "Invoice I have Col A with product code and need to fill in Cols B and C with relevant data from sheet "Product"

I hope this makes sense.

BTW I use 2003 but am trying to keep it compatible with 2007/2010

Thanks for the ongoing help from this forum

Alan
 
Sort by date Sort by votes
Ok, let's try. First some general comments about your code. I know it is only a small bit and it is a sample, but it is all I have to go on.

1. You have Dim'd some variables that are not used in the code (vTaxYN, vPrice) and not Dim'd some you have used (i, j) so they will be treated a Variant type. I recommend explicitly declaring all variables so I would declare i & j as Long in your code.

2. Speed seems to be an issue for you so turning off ScreenUpdating while your code is interacting with your worksheet would help considerably. Depending on what is in your Invoice sheet, turning off automatic calculation may also help.

3. Your sample code run on the data in post #3 does not produce the expected results shown in post #3. I think you probably meant this
Rich (BB code): 
If vProductCode = wsProd.Cells(j, 1) Then

4. Before we move to arrays (you don't seem that keen anyway), you should be able to speed the code considerably by avoiding looping through every row in the Product sheet for every product in the Invoice sheet by using the Find method instead. Here is some code that should do that. You might test it on a copy of your larger sample to compare speed.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test3()<br>    <SPAN style="color:#00007F">Dim</SPAN> vProductCode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsProd <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wsInv <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> FinalRowProd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FinalRowInv <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ProdFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Prod <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LookUpRng <SPAN style="color:#00007F">As</SPAN> Range<br> <br>    <SPAN style="color:#00007F">Set</SPAN> wsProd = Worksheets("Product")<br>    <SPAN style="color:#00007F">Set</SPAN> wsInv = Worksheets("Invoice")<br> <br>    FinalRowProd = wsProd.Cells(Rows.Count, 1).End(xlUp).Row<br>    FinalRow<SPAN style="color:#00007F">In</SPAN>v = wsInv.Cells(Rows.Count, 1).End(xlUp).Row<br>    <SPAN style="color:#00007F">Set</SPAN> LookUpRng = wsProd.Range("A1").Resize(FinalRowProd)<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Prod In wsInv.Range("A1").Resize(FinalRowInv).Cells<br>        vProductCode = Prod.Value<br>        <SPAN style="color:#00007F">Set</SPAN> ProdFound = LookUpRng.Find(What:=vProductCode, _<br>            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ProdFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Prod.Offset(, 1).Resize(, 2).Value _<br>                = ProdFound.Offset(, 1).Resize(, 2).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Prod<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi

Rather than cycle through all the Products in the Product sheet use Match and Two Index formulae as follows -

In cell D1 of the Product sheet enter -
Code: 
=MATCH($E1,$A$2:$A$6,0)

In cell E1 -
Product code

In cell F1 enter -
Code: 
=INDEX($B$2:$C4,$D$1,1)

In cell G1 enter -
Code: 
=INDEX($B$2:$C4,$D$1,2)

Then in your code you remove the imbedded loop through the Product sheet and replace it with code to -

1, Set the Product code in E1 with the Product code from your Invoice sheet and

2, To capture the result in cells F1 and G1 to complete the info in your Invoice sheet.

hth
 
Upvote 0
Peter is right when he say you should declare your variables

Sinmply insert Option Explicit as the very first line of code

You may also want to use Option Compate text This makes all string comparison case insensetive

Just a suggestion

PS Take Peter's advise - his code is usually very efficient
 
Upvote 0
Hi Guys,

Thanks so much for the help.
Peter, I'm busy experimenting with what you've done. Certainly works a great deral better than what I've done thus far.

Code: 
[FONT=Courier][COLOR=#00007f]If[/COLOR] [COLOR=#00007f]Not[/COLOR] ProdFound [COLOR=#00007f]Is[/COLOR] [COLOR=#00007f]Nothing[/COLOR] [COLOR=#00007f]Then[/COLOR]
            Prod.Offset(, 1).Resize(, 2).Value _
                = ProdFound.Offset(, 1).Resize(, 2).Value[/FONT]
One question thus far, would the above code still work if on one sheet (in this case Product) one had the table laid out across the page (ie P1 in A1; Y in A2; 33 in A3 etc) whilst still having the invoice page as is. Something like the following??

Code: 
[FONT=Courier][COLOR=#00007f]If[/COLOR] [COLOR=#00007f]Not[/COLOR] ProdFound [COLOR=#00007f]Is[/COLOR] [COLOR=#00007f]Nothing[/COLOR] [COLOR=#00007f]Then[/COLOR]
            Prod.Offset(, 1).Resize(, 2).Value _
                = ProdFound.Offset(1, 0).Resize(2, 0).Value[/FONT]

Once again, thanks a million for your time and trouble. For a newbie like me the help I get from this forum is beyond measure.

Alan
 
Upvote 0
If the Product sheet is arranged horizontally and Invoice vertically then a few things would have to change. We'd need

- FinalColProd instead of FinalRowProd

- To define the LookUpRng as a horizontal range rather than a vertical one

- Transpose the data as it is moved from Product sheet to Invoice sheet.

Here is some modified code to try (altered lines marked), together with sample of Product sheet and Invoice sheet (after code has been run).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test4()<br>    <SPAN style="color:#00007F">Dim</SPAN> vProductCode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsProd <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wsInv <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> FinalColProd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> <SPAN style="color:#007F00">'***Changed</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FinalRowInv <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ProdFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Prod <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LookUpRng <SPAN style="color:#00007F">As</SPAN> Range<br> <br>    <SPAN style="color:#00007F">Set</SPAN> wsProd = Worksheets("Product")<br>    <SPAN style="color:#00007F">Set</SPAN> wsInv = Worksheets("Invoice")<br> <br>    FinalColProd = wsProd.Cells(1, Columns.Count).End(xlToLeft).Column <SPAN style="color:#007F00">'***Changed</SPAN><br>    FinalRow<SPAN style="color:#00007F">In</SPAN>v = wsInv.Cells(Rows.Count, 1).End(xlUp).Row<br>    <SPAN style="color:#00007F">Set</SPAN> LookUpRng = wsProd.Range("A1").Resize(1, FinalColProd) <SPAN style="color:#007F00">'***Changed</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Prod In wsInv.Range("A1").Resize(FinalRowInv).Cells<br>        vProductCode = Prod.Value<br>        <SPAN style="color:#00007F">Set</SPAN> ProdFound = LookUpRng.Find(What:=vProductCode, _<br>            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ProdFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Prod.Offset(, 1).Resize(, 2).Value _<br>                = Application.Transpose(ProdFound.Offset(1).Resize(2).Value) <SPAN style="color:#007F00">'***Changed</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Prod<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Excel Workbook
ABCDEF
1P1P2P3P4P5
2YNNYN
31112131415
4
Product



Excel Workbook
ABCD
1P1Y11
2P3N13
3P5N15
4P4Y14
5P2N12
6
Invoice
 
Upvote 0
Hi Peter,

Thanks for this. I think I'm getting the gist of it now. I will spend a little time now trying to apply it to the "real" problem and will let you know.

If I can get it to work it I think a somewhat expanded version of your code will allow me to replace a routine of 17 lines which I am currently running 34 times!!!

Thanks again for your help. I cant express how grateful I am.

Alan
 
Upvote 0
Hi Peter,
I'm making big strides thanks to your help. My next question is how to I use your look up method to basically get data from non consecutive columns.

Basically, looking at rows 2 and 3 below I collect the first 8 columns at read them into a "header row" - no problem

I then need to make up a vertical list of products where every third column contains the "code" than goes in col a, a true /false, in col B and another true/false in col c



IndexQuoteMB7877

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 85px"><COL style="WIDTH: 145px"><COL style="WIDTH: 58px"><COL style="WIDTH: 72px"><COL style="WIDTH: 42px"><COL style="WIDTH: 64px"><COL style="WIDTH: 55px"><COL style="WIDTH: 61px"><COL style="WIDTH: 44px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 38px"><COL style="WIDTH: 51px"><COL style="WIDTH: 53px"><COL style="WIDTH: 47px"><COL style="WIDTH: 52px"><COL style="WIDTH: 49px"><COL style="WIDTH: 40px"><COL style="WIDTH: 55px"><COL style="WIDTH: 56px"><COL style="WIDTH: 48px"><COL style="WIDTH: 52px"><COL style="WIDTH: 54px"><COL style="WIDTH: 45px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD></TR><TR style="HEIGHT: 51px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Component Code</TD><TD style="FONT-WEIGHT: bold">Component Description</TD><TD style="FONT-WEIGHT: bold">Index Code</TD><TD style="FONT-WEIGHT: bold">Base Price</TD><TD style="FONT-WEIGHT: bold">Index Price Point</TD><TD style="FONT-WEIGHT: bold">Base Date</TD><TD style="FONT-WEIGHT: bold">Index at Base</TD><TD style="FONT-WEIGHT: bold">Index Date</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Fixed</TD><TD style="FONT-WEIGHT: bold">Fixed Esc</TD><TD style="FONT-WEIGHT: bold">Fixed Fx</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Fuel</TD><TD style="FONT-WEIGHT: bold">Fuel Esc</TD><TD style="FONT-WEIGHT: bold">Fuel Fx</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Trans -port</TD><TD style="FONT-WEIGHT: bold">Trans -port Esc</TD><TD style="FONT-WEIGHT: bold">Trans -port Fx</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Cement</TD><TD style="FONT-WEIGHT: bold">Cement Esc</TD><TD style="FONT-WEIGHT: bold">Cement Fx</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Bricks</TD><TD style="FONT-WEIGHT: bold">Bricks Esc</TD><TD style="FONT-WEIGHT: bold">Bricks Fx</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Gravel</TD><TD style="FONT-WEIGHT: bold">Gravel Esc</TD><TD style="FONT-WEIGHT: bold">Gravel Fx</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>PedBridge11</TD><TD>Bridge, Pedestrian, Steel, Small</TD><TD>STEEL1</TD><TD style="TEXT-ALIGN: right">100.00</TD><TD>P08</TD><TD style="TEXT-ALIGN: right">11/07/01</TD><TD style="TEXT-ALIGN: right">1,000.00</TD><TD style="TEXT-ALIGN: right">Aug/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">3.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #cc99ff">16</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #cc99ff">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #cc99ff">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc99">3.5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc99">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc99">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffff">25</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffff">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffff">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>BuildRes02</TD><TD>Building, Residential, Grade 2</TD><TD>BIFSA1</TD><TD style="TEXT-ALIGN: right">125,000.00</TD><TD>P06</TD><TD style="TEXT-ALIGN: right">11/06/01</TD><TD style="TEXT-ALIGN: right">1,000.00</TD><TD style="TEXT-ALIGN: right">Jun/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR></TBODY></TABLE>
Using this I then build up an invoice something like this

TestInv

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 86px"><COL style="WIDTH: 122px"><COL style="WIDTH: 73px"><COL style="WIDTH: 75px"><COL style="WIDTH: 74px"><COL style="WIDTH: 80px"><COL style="WIDTH: 84px"><COL style="WIDTH: 72px"><COL style="WIDTH: 49px"><COL style="WIDTH: 69px"><COL style="WIDTH: 69px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Quote Ref</TD><TD style="FONT-WEIGHT: bold">Cust Ref</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold">Quote Date</TD><TD style="FONT-WEIGHT: bold">Invoice Date</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>MB7877</TD><TD>Ref MayBeeRight</TD><TD>May Bee</TD><TD style="TEXT-ALIGN: right">25-Feb-11</TD><TD>P02</TD><TD style="TEXT-ALIGN: right">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 68px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-WEIGHT: bold">Code</TD><TD style="FONT-WEIGHT: bold">Code Description</TD><TD style="FONT-WEIGHT: bold">Total Amount</TD><TD style="FONT-WEIGHT: bold">Index Code</TD><TD style="FONT-WEIGHT: bold">Index Descr</TD><TD style="FONT-WEIGHT: bold">Base Fx Rate</TD><TD style="FONT-WEIGHT: bold">Fx Rate Now</TD><TD style="FONT-WEIGHT: bold">Base Index Value</TD><TD style="FONT-WEIGHT: bold">Current Index Value</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-WEIGHT: bold">PedBridge11</TD><TD style="FONT-WEIGHT: bold">Bridge, Pedestrian, Steel, Small</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">100.00</TD><TD style="FONT-WEIGHT: bold">STEEL1</TD><TD style="FONT-WEIGHT: bold">Bridge, Pedestrian, Steel, Small</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">10.0000</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">20.0000</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">100.00</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">174</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-WEIGHT: bold">Component</TD><TD style="FONT-WEIGHT: bold">Percentage</TD><TD style="FONT-WEIGHT: bold">Escalate T/F</TD><TD style="FONT-WEIGHT: bold">FX Esc T/F</TD><TD style="FONT-WEIGHT: bold">Calc at Base</TD><TD style="FONT-WEIGHT: bold">Conv at Base Fx</TD><TD style="FONT-WEIGHT: bold">Conv at Current Fx</TD><TD style="FONT-WEIGHT: bold">Index Converted</TD><TD style="FONT-WEIGHT: bold">Tax Y/N</TD><TD style="FONT-WEIGHT: bold">Sales Tax</TD><TD style="FONT-WEIGHT: bold">Total</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Fixed</TD><TD style="TEXT-ALIGN: right">3.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">3.00</TD><TD style="TEXT-ALIGN: right">30.00</TD><TD style="TEXT-ALIGN: right">30.00</TD><TD style="TEXT-ALIGN: right">30.00</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">4.95</TD><TD style="TEXT-ALIGN: right">34.95</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>Fuel</TD><TD style="TEXT-ALIGN: right">11.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">11.00</TD><TD style="TEXT-ALIGN: right">110.00</TD><TD style="TEXT-ALIGN: right">110.00</TD><TD style="TEXT-ALIGN: right">191.40</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">31.58</TD><TD style="TEXT-ALIGN: right">222.98</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>Transport</TD><TD style="TEXT-ALIGN: right">16.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">16.00</TD><TD style="TEXT-ALIGN: right">160.00</TD><TD style="TEXT-ALIGN: right">160.00</TD><TD style="TEXT-ALIGN: right">278.40</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">45.94</TD><TD style="TEXT-ALIGN: right">324.34</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Cement</TD><TD style="TEXT-ALIGN: right">3.50</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">3.50</TD><TD style="TEXT-ALIGN: right">35.00</TD><TD style="TEXT-ALIGN: right">70.00</TD><TD style="TEXT-ALIGN: right">121.80</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">20.10</TD><TD style="TEXT-ALIGN: right">141.90</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Bricks</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">50.00</TD><TD style="TEXT-ALIGN: right">50.00</TD><TD style="TEXT-ALIGN: right">50.00</TD><TD>N</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">50.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Gravel</TD><TD style="TEXT-ALIGN: right">25.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">25.00</TD><TD style="TEXT-ALIGN: right">250.00</TD><TD style="TEXT-ALIGN: right">250.00</TD><TD style="TEXT-ALIGN: right">250.00</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">41.25</TD><TD style="TEXT-ALIGN: right">291.25</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Paint</TD><TD style="TEXT-ALIGN: right">23.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">23.00</TD><TD style="TEXT-ALIGN: right">230.0000</TD><TD style="TEXT-ALIGN: right">460.0000</TD><TD style="TEXT-ALIGN: right">800.40</TD><TD>N</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">800.40</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Electrical</TD><TD style="TEXT-ALIGN: right">8.50</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">8.50</TD><TD style="TEXT-ALIGN: right">85.0000</TD><TD style="TEXT-ALIGN: right">170.0000</TD><TD style="TEXT-ALIGN: right">170.00</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">28.05</TD><TD style="TEXT-ALIGN: right">198.05</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>Plumbing</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">40.00</TD><TD style="TEXT-ALIGN: right">40.00</TD><TD style="TEXT-ALIGN: right">69.60</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">11.48</TD><TD style="TEXT-ALIGN: right">81.08</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>Labour</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">20.00</TD><TD style="TEXT-ALIGN: right">20.00</TD><TD>N</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">20.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="FONT-WEIGHT: bold">Component Total PedBridge11</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">100.00</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1,000.00</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1,360.00</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1,981.60</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">183.35</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">2,164.95</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 68px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="FONT-WEIGHT: bold">Code</TD><TD style="FONT-WEIGHT: bold">Code Description</TD><TD style="FONT-WEIGHT: bold">Total Amount</TD><TD style="FONT-WEIGHT: bold">Index Code</TD><TD style="FONT-WEIGHT: bold">Index Descr</TD><TD style="FONT-WEIGHT: bold">Base Fx Rate</TD><TD style="FONT-WEIGHT: bold">Fx Rate Now</TD><TD style="FONT-WEIGHT: bold">Base Index Value</TD><TD style="FONT-WEIGHT: bold">Current Index Value</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="FONT-WEIGHT: bold">BuildRes02</TD><TD style="FONT-WEIGHT: bold">Building, Residential, Grade 2</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">125,000.00</TD><TD style="FONT-WEIGHT: bold">BIFSA1</TD><TD style="FONT-WEIGHT: bold">Building, Residential, Grade 2</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">23.4455</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">20.0000</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">125,000.00</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD style="FONT-WEIGHT: bold">Component</TD><TD style="FONT-WEIGHT: bold">Percentage</TD><TD style="FONT-WEIGHT: bold">Escalate T/F</TD><TD style="FONT-WEIGHT: bold">FX Esc T/F</TD><TD style="FONT-WEIGHT: bold">Calc at Base</TD><TD style="FONT-WEIGHT: bold">Conv at Base Fx</TD><TD style="FONT-WEIGHT: bold">Conv at Current Fx</TD><TD style="FONT-WEIGHT: bold">Index Converted</TD><TD style="FONT-WEIGHT: bold">Tax Y/N</TD><TD style="FONT-WEIGHT: bold">Sales Tax</TD><TD style="FONT-WEIGHT: bold">Total</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD>Gravel</TD><TD style="TEXT-ALIGN: right">8.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">10,000.00</TD><TD style="TEXT-ALIGN: right">234,455.00</TD><TD style="TEXT-ALIGN: right">200,000.00</TD><TD style="TEXT-ALIGN: right">243.20</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">40.13</TD><TD style="TEXT-ALIGN: right">283.33</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD>Electrical</TD><TD style="TEXT-ALIGN: right">18.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">22,500.00</TD><TD style="TEXT-ALIGN: right">527,523.75</TD><TD style="TEXT-ALIGN: right">527,523.75</TD><TD style="TEXT-ALIGN: right">641.47</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">105.84</TD><TD style="TEXT-ALIGN: right">747.31</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD>Steel</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">6,250.00</TD><TD style="TEXT-ALIGN: right">146,534.38</TD><TD style="TEXT-ALIGN: right">146,534.38</TD><TD style="TEXT-ALIGN: right">178.19</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">29.40</TD><TD style="TEXT-ALIGN: right">207.59</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD>Glass</TD><TD style="TEXT-ALIGN: right">6.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">7,500.00</TD><TD style="TEXT-ALIGN: right">175,841.25</TD><TD style="TEXT-ALIGN: right">150,000.00</TD><TD style="TEXT-ALIGN: right">150,000.00</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">24,750.00</TD><TD style="TEXT-ALIGN: right">174,750.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD>Paving</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">2,500.00</TD><TD style="TEXT-ALIGN: right">58,613.75</TD><TD style="TEXT-ALIGN: right">50,000.00</TD><TD style="TEXT-ALIGN: right">50,000.00</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">8,250.00</TD><TD style="TEXT-ALIGN: right">58,250.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD>Trenching</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD style="TEXT-ALIGN: right">2,500.00</TD><TD style="TEXT-ALIGN: right">58,613.75</TD><TD style="TEXT-ALIGN: right">50,000.00</TD><TD style="TEXT-ALIGN: right">50,000.00</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">8,250.00</TD><TD style="TEXT-ALIGN: right">58,250.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD>Plastering</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">5,000.00</TD><TD style="TEXT-ALIGN: right">117,227.50</TD><TD style="TEXT-ALIGN: right">117,227.50</TD><TD style="TEXT-ALIGN: right">117,227.50</TD><TD>Y</TD><TD style="TEXT-ALIGN: right">19,342.54</TD><TD style="TEXT-ALIGN: right">136,570.04</TD></TR></TBODY></TABLE>

ie Inv A 17 gets data from Prod I 1
Inv B 17 gets data from Prod I2
Inv C 17 gats data from Prod J2
Inv D17 gets data from Prod K2
Inv A18 gets data from Prod L1
inv B18 gets data from Prod L2 etc.

I can get required data on individual basis - ie if I just wanted data from I, J and K no problem but I am at a loss to figure out this next step.

Sorry to impose so much on your time.

Regards,

Alan
 
Upvote 0
Maybe not what you are looking for, but here is array approach to the simple match index posted earlier.


Excel Workbook
ABCDEFGHIJKL
1Invoice #DateLine#ItemqtyPriceExtension*****
2Inv-14/1/20111item-823570*Inv #Date# of LinesValue
3Inv-14/1/20112item-67100700*Inv-14/2/201110$ *5,370.00
4Inv-14/1/20113item-31068680*Inv-24/3/20119$10,816.00
5Inv-14/1/20114item-132019380*Inv-34/8/20118$ *5,896.00
6Inv-14/1/20115item-530732190*****
7Inv-14/1/20116item-141019190*****
8Inv-14/2/20117item-162019380*Beg Date4/2/2011**
9Inv-14/2/20118item-42020400*End Date:4/4/2011**
10Inv-14/2/20119item-151019190*ItemQtyCost / UnitTot Cost
11Inv-14/2/201110item-171019190*item-10**
12Inv-24/2/20111item-51073730*item-100**
13Inv-24/2/20112item-840351400*item-1110**
14Inv-24/2/20113item-72024480*item-130**
15Inv-24/3/20114item-1741976*item-140**
16Inv-24/3/20115item-8435140*item-1510**
17Inv-24/3/20116item-550733650*item-1620**
18Inv-24/3/20117item-550733650*item-1714**
19Inv-24/3/20118item-111019190*item-30**
20Inv-24/3/20119item-65100500*item-420**
21Inv-34/8/20111item-132019380*item-5110**
22Inv-34/8/20112item-6101001000*item-65**
23Inv-34/8/20113item-171019190*item-720**
24Inv-34/8/20114item-11019190*item-844**
25Inv-34/8/20115item-940742960*item-90**
26Inv-34/8/20116item-82035700*#N/A#N/A**
27Inv-34/8/20117item-1041976*****
28Inv-34/8/20118item-64100400*****
29Inv-44/8/20119item-6501005000*****
Sheet2


the invoice, item refer to the range as shown in header of raw data
 
Upvote 0
Hi Snoopy,
Thanks for the response. I am new to VBA and honestly do not understand the logic behind your solution. For example you have formulae in I3, J3 where those fields Inv 1 and a date. I am probably missing the point altogether - if so please forgive my ignorance.
In any event part of my problem is there can be anything from 1 to a virtually unlimited number of lines on the Product page. This is part of the reason why I am hesitant to rely on formulae and formatting of the sheet itself. Without using VBA I doubt that I can achieve this.

Thanks anyway for your i9nterest and the obvious amount of time and thought you have invested.

Thanks,

Alan
 
Upvote 0
I'm not exactly sure what you are after, but see if you can modify this to suit your needs. From the data you actually posted for sheet 'IndexQuoteMB7877' the code produces this (note some rows hidden):

Excel Workbook
ABCD
16
17Fixed3FALSEFALSE
18Fuel11TRUEFALSE
19Trans -port16TRUEFALSE
20Cement3.5TRUETRUE
21Bricks5FALSEFALSE
22Gravel25FALSEFALSE
23
24
33
34Fixed0FALSEFALSE
35Fuel0FALSEFALSE
36Trans -port0FALSEFALSE
37Cement0FALSEFALSE
38Bricks0FALSEFALSE
39Gravel8TRUETRUE
40
TestInv



<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Test5()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsInd <SPAN style="color:#00007F">As</SPAN> Worksheet, wsInv <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> FinalRowInd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, FinalColInd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> BaseCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FirstColInd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 9<br>    <SPAN style="color:#00007F">Const</SPAN> FirstRowInv <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 17<br>    <SPAN style="color:#00007F">Const</SPAN> RowGapInv <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 17<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsInd = Sheets("IndexQuoteMB7877")<br>    <SPAN style="color:#00007F">Set</SPAN> wsInv = Sheets("TestInv")<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> wsInd<br>        FinalRowInd = .Cells(.Rows.Count, 1).End(xlUp).Row<br>        FinalColInd = .Cells(1, .Columns.Count).End(xlToLeft).Column<br>        <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> FinalRowInd<br>            i = 0<br>            <SPAN style="color:#00007F">For</SPAN> c = FirstColInd <SPAN style="color:#00007F">To</SPAN> FinalColInd <SPAN style="color:#00007F">Step</SPAN> 3<br>                <SPAN style="color:#00007F">Set</SPAN> BaseCell = .Cells(1, c)<br>                <SPAN style="color:#00007F">With</SPAN> wsInv.Cells(FirstRowInv + (r - 2) * RowGapInv + i, 1)<br>                    .Value = BaseCell.Value<br>                    .Offset(, 1).Resize(, 3).Value = BaseCell.Offset(r - 1).Resize(, 3).Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                i = i + 1<br>            <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">Next</SPAN> r<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Building Custom Function with Array Loop
Replies
3
Views
101
FormR
FormR
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
134
montecarlo2012
montecarlo2012
franklin_m
  • Solved
ReDim array w/ variables
Replies
3
Views
490
franklin_m
franklin_m
I
  • Question Question
Unable to run VBA code
Replies
6
Views
141
RoryA
RoryA
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
107
Mazurofgod
M

Forum statistics

Threads
1,224,592
Messages
6,179,787
Members
452,942
Latest member
VijayNewtoExcel

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