Range Offsett Optimizing Macro

W

WuAhUuU

Board Regular
Joined
Sep 16, 2008
Messages
66
I need help optimizing the macro below. As you can see below, I have a vlookup funtion formula. My question is how can I optimize the macros starting at the red. Each code look at a different range and then pastes the value at a different column. Need help using probably the offset function. The lookup part lookups values between 12 arrays with 2 columns each which the left column is dates, the right column is price. If you need further information please let me know and I will try to put more information here.

Code: 
Sub OrganizeData()
Set Data = Worksheets("Data")
Set Order = Worksheets("Order")

SilenceExcel [COLOR=darkgreen]'THIS MACRO WORKS
[/COLOR]Order.Range("A2:AA5000").ClearContents

For i = 0 To Data.Cells(2, 27) - 1
    With Order
        .Cells(i + 2, 1) = Data.Cells(2 + i, 28)
    End With

    [COLOR=red]With Order.Cells(i + 2, 2)
        .Formula = "=IFERROR(VLOOKUP($A" & (i + 2) & ",Data!$AD$2:$AE$50000,2),B" & (i + 1) & ")"
        .Value = .Value
    End With[/COLOR]

    With Order.Cells(i + 2, 3)
        .Formula = "=IFERROR(VLOOKUP($A" & (i + 2) & ",Data!$AF$2:$AG$50000,2),C" & (i + 1) & ")"
        .Value = .Value
    End With

    With Order.Cells(i + 2, 4)
        .Formula = "=IFERROR(VLOOKUP($A" & (i + 2) & ",Data!$Ah$2:$Ai$50000,2),D" & (i + 1) & ")"
        .Value = .Value
    End With

Next i

WakeExcel [COLOR=#006400]'THIS MACRO WORKS[/COLOR]
End Sub

FROM THE DATA SHEET
<TABLE style="WIDTH: 291pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=387><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; WIDTH: 97pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl675 height=15 width=129 colSpan=2>SPX Index</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 97pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl676 width=129 colSpan=2>EC1 Curncy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 97pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl676 width=129 colSpan=2>JY1 Curncy</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl672 height=15 align=right>02/28/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 align=right>1280.66</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>02/28/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>1.1929</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>02/28/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>86.55</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 height=15 align=right>03/01/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 align=right>1291.24</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/01/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>1.1925</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/01/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>86.23</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 height=15 align=right>03/02/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 align=right>1289.14</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/02/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>1.204</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/02/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>86.43</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 height=15 align=right>03/03/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 align=right>1287.23</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/03/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>1.2038</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/03/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>85.95</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 height=15 align=right>03/06/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 align=right>1278.26</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/06/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>1.2024</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/06/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>85.17</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 height=15 align=right>03/07/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 align=right>1275.88</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/07/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>1.1891</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl670 align=right>03/07/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl669 align=right>84.86</TD></TR></TBODY></TABLE>

FROM THE ORDER SHEET
<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=196><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; WIDTH: 51pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl674 height=15 width=68> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 width=64>EURO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 width=64>YEN</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl672 height=15>02/28/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>1.1929</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>86.55</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 height=15>03/01/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>1.1925</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>86.23</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 height=15>03/02/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>1.204</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>86.43</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 height=15>03/03/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>1.2038</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>85.95</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 height=15>03/06/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>1.2024</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>85.17</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: black; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl673 height=15>03/07/2006</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>1.1891</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl671 align=right>84.86</TD></TR></TBODY></TABLE>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
After adding to your code all it is doing is clearing the contents on the Order sheet, what are you looking to do, once the data has been cleared?

Sub OrganizeData()
Dim Data As Worksheet
Dim order As Worksheet
Dim i As Integer
Set Data = Worksheets("Data")
Set order = Worksheets("Order")
'SilenceExcel 'THIS MACRO WORKS
order.Range("A2:AA5000").ClearContents
For i = 0 To Data.Cells(2, 27) - 1
With order
.Cells(i + 2, 1) = Data.Cells(2 + i, 28)
End With
With order.Cells(i + 2, 2)
.Formula = "=IFERROR(VLOOKUP($A" & (i + 2) & ",Data!$AD$2:$AE$50000,2),B" & (i + 1) & ")"
.Value = .Value
End With
With order.Cells(i + 2, 3)
.Formula = "=IFERROR(VLOOKUP($A" & (i + 2) & ",Data!$AF$2:$AG$50000,2),C" & (i + 1) & ")"
.Value = .Value
End With
With order.Cells(i + 2, 4)
.Formula = "=IFERROR(VLOOKUP($A" & (i + 2) & ",Data!$Ah$2:$Ai$50000,2),D" & (i + 1) & ")"
.Value = .Value
End With
Next i
'WakeExcel 'THIS MACRO WORKS
End Sub
Click to expand...
 
Upvote 0
Thank you Trevor. What I am looking for is a code where I can use the offset since as you can see from the data below. I find the values from a two column array and then paste the price value on the Order sheet so one next to the other.
 
Upvote 0
Yes I can see that, but your code clears the content so no values are in the sheet. If you had dates in column A you can then do something like this: When you add some dates in it will fill the cells accordingly

Sub mcrFillCells()

' IF Statement with VLookup to fill cells

ActiveCell.FormulaR1C1 = "=IF(RC1>0,VLOOKUP(RC1,Data!R1C1:R7C6,4,FALSE),"""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B7")
Range("B2:B7").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC1>0,VLOOKUP(RC1,Data!R1C1:R7C6,6,FALSE),"""")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C7")
Range("C2:C7").Select
Range("A1").Select
End Sub
Click to expand...

Before running this macro the data shows this

Order

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 73px"><COL style="WIDTH: 165px"><COL style="WIDTH: 38px"></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></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt">EURO</TD><TD style="FONT-SIZE: 10pt">YEN</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Once run it shows this

Order

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 73px"><COL style="WIDTH: 165px"><COL style="WIDTH: 38px"></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></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt">EURO</TD><TD style="FONT-SIZE: 10pt">YEN</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=IF($A2>0,VLOOKUP($A2,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C2</TD><TD>=IF($A2>0,VLOOKUP($A2,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B3</TD><TD>=IF($A3>0,VLOOKUP($A3,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C3</TD><TD>=IF($A3>0,VLOOKUP($A3,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B4</TD><TD>=IF($A4>0,VLOOKUP($A4,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C4</TD><TD>=IF($A4>0,VLOOKUP($A4,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B5</TD><TD>=IF($A5>0,VLOOKUP($A5,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C5</TD><TD>=IF($A5>0,VLOOKUP($A5,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B6</TD><TD>=IF($A6>0,VLOOKUP($A6,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C6</TD><TD>=IF($A6>0,VLOOKUP($A6,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B7</TD><TD>=IF($A7>0,VLOOKUP($A7,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C7</TD><TD>=IF($A7>0,VLOOKUP($A7,Data!$A$1:$F$7,6,FALSE),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Add some dates and you get this:

Order

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 73px"><COL style="WIDTH: 165px"><COL style="WIDTH: 38px"></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></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #000000; FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt">EURO</TD><TD style="FONT-SIZE: 10pt">YEN</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">02/28/2006</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">1.1929</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">86.6</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">03/01/2006</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">1.1925</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">86.2</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">03/02/2006</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">1.204</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">86.4</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">03/03/2006</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">1.2038</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">86</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">03/06/2006</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">1.2024</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">85.2</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">03/07/2006</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">1.1891</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 10pt">84.9</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=IF($A2>0,VLOOKUP($A2,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C2</TD><TD>=IF($A2>0,VLOOKUP($A2,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B3</TD><TD>=IF($A3>0,VLOOKUP($A3,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C3</TD><TD>=IF($A3>0,VLOOKUP($A3,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B4</TD><TD>=IF($A4>0,VLOOKUP($A4,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C4</TD><TD>=IF($A4>0,VLOOKUP($A4,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B5</TD><TD>=IF($A5>0,VLOOKUP($A5,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C5</TD><TD>=IF($A5>0,VLOOKUP($A5,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B6</TD><TD>=IF($A6>0,VLOOKUP($A6,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C6</TD><TD>=IF($A6>0,VLOOKUP($A6,Data!$A$1:$F$7,6,FALSE),"")</TD></TR><TR><TD>B7</TD><TD>=IF($A7>0,VLOOKUP($A7,Data!$A$1:$F$7,4,FALSE),"")</TD></TR><TR><TD>C7</TD><TD>=IF($A7>0,VLOOKUP($A7,Data!$A$1:$F$7,6,FALSE),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Prior to calculating the VLOOKUP functions it gets the date column from DATA sheet. Right after the clear step.

That is why I am looking for an offset function to grab the price in the two column array from DATA and place it in the ORDER sheet depending on the date and on a single column array.

Thanx
 
Upvote 0
Try this based on your sample, the first part selects the data sheet goes into c2 then selects all the cells below and copies them.

Then goes to the Order sheet and into cell A2 and paste in the values

Then it places in the formula, if you need to extend the formula just alter the range.

Sub Macro4()
Sheets("Data").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Order").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B2").Select

ActiveCell.FormulaR1C1 = "=IF(RC1>0,VLOOKUP(RC1,Data!R1C1:R7C6,4,FALSE),"""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B7")
Range("B2:B7").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC1>0,VLOOKUP(RC1,Data!R1C1:R7C6,6,FALSE),"""")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C7")
Range("C2:C7").Select
Range("A1").Select
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
T
  • Question Question
Exporting Cell Range As PNG - Getting A White Border To The Bottom & Right - Why?
2
Replies
16
Views
197
jolivanes
jolivanes
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,603
Messages
6,179,853
Members
452,948
Latest member
UsmanAli786

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