Combining code to make smaller

[Djblois]

I have stepped back from additional features from my add-in and trying to make it smaller, faster, and more reliable all togeather. I will give up size if it slows it down. Can I combine this code? First here:

[VBA]reOrder.Copy After:=Sheets(1)
ActiveSheet.Name = "Original"[/VBA]

Next, In many cases I do a formula then I copy and paste values over it. Can I combine it into one shorter, quicker step?

[VBA]Range("B4").FormulaR1C1 = "=Mid(RC[-1],14,11)"
Range("B4").Value = Range("B4").Value[/VBA]

Also, many places I add many column headings, can I add them all with an array? and if I can would it be quicker?

[VBA]Range("A1").FormulaR1C1 = "Whse Region"
Range("B1").FormulaR1C1 = "Equiv Code"
Range("C1").FormulaR1C1 = "Product"
Range("D1").FormulaR1C1 = "On-Order FYI"
Range("E1").FormulaR1C1 = "Inventory Available"
Range("F1").FormulaR1C1 = "In Transfer"[/VBA]

[feathers212]

Can I combine this code? First here:

[vba]reOrder.Copy After:=Sheets(1)
ActiveSheet.Name = "Original"[/vba]

I'm not sure about this one. Anyone else?

Next, In many cases I do a formula then I copy and paste values over it. Can I combine it into one shorter, quicker step?

Try putting the formula within the code:
[vba]Range("B4").Value = Mid(Range("A4"),14,11)[/vba]

Also, many places I add many column headings, can I add them all with an array? and if I can would it be quicker?

If this same code is used over and over, you could create a module and then just call that module code from the current code. Otherwise, I'm not all that familiar with arrays....perhaps someone else can help me out again?

[Djblois]

No not that same code or I would have created a sperate module for it but I do different column headings for different reports. That is just one example.

[Bob Phillips]

Also, many places I add many column headings, can I add them all with an array? and if I can would it be quicker?

[vba]Range("A1").FormulaR1C1 = "Whse Region"
Range("B1").FormulaR1C1 = "Equiv Code"
Range("C1").FormulaR1C1 = "Product"
Range("D1").FormulaR1C1 = "On-Order FYI"
Range("E1").FormulaR1C1 = "Inventory Available"
Range("F1").FormulaR1C1 = "In Transfer"[/vba]

[vba]

Range("A1:F1").Value = Array("Whse Region", "Equiv Code", "Product", "On-Order FYI", "Inventory Available", "In Transfer")

[/vba]

[Djblois]

I'm not sure about this one. Anyone else?


Try putting the formula within the code:
[vba]Range("B4").Value = Mid(Range("A4"),14,11)[/vba]


If this same code is used over and over, you could create a module and then just call that module code from the current code. Otherwise, I'm not all that familiar with arrays....perhaps someone else can help me out again?

I can't get this to work with another formula:

[VBA]Range("E2:E" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-1],'[BusinessReportingReference.xls]Whses'!C1:C8,2,FALSE)"[/VBA]

[Bob Phillips]

That is because you have used A1 notation in an R1C1 formula.

[Djblois]

THis line of code works:

[VBA]

Range("E2:E" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-1],'[BusinessReportingReference.xls]Whses'!C1:C8,2,FALSE)"

[/VBA]

THis one doesn't:

[VBA]

Range("E2:E" & finalRow).value = VLOOKUP(RC[-1],'[BusinessReportingReference.xls]Whses'!C1:C8,2,FALSE)

[/VBA]

[Bob Phillips]

It is not a string, you can't issue VLOOKUP from VBA.

[Djblois]

So, I can't use that code???

[Bob Phillips]

Use the one that works, ie with a properly formed string.

[mdmackillop]

Rather than VLookup, why not use find.

[VBA]Option Explicit

Sub Lookup()
Dim WB As Workbook, WS As Worksheet, Rng As Range
Set WB = Workbooks("Book2.xls")
Set WS = WB.Sheets(1)
Set Rng = WS.Range("C1:C8")

Cells(1, 1) = Rng.Find("Data4").Offset(, 1)
'or as a function
Cells(2, 1) = VLook("Data3", Rng, 2)

End Sub


Function VLook(x As Variant, Rng As Range, OSet As Long)
VLook = Rng.Find(x).Offset(, OSet)
End Function

[/VBA]

[Djblois]

mdmack,

but how can I use that for a whole column? Also do you know if it will run quicker than a vlookup? if you don't know if it is quicker just help me run it on a whole column and I will test it.

[mdmackillop]

No idea if it's quicker.
[VBA]For Each cel In Range("E2:E" & finalRow)
cel = Rng.Find(cel.Offset(, -1), , , xlWhole).Offset(, 1)
Next
[/VBA]