PDA

View Full Version : Combining code to make smaller



Djblois
04-19-2007, 06:21 AM
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:

reOrder.Copy After:=Sheets(1)
ActiveSheet.Name = "Original"

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?

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

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

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"

feathers212
04-19-2007, 06:42 AM
Can I combine this code? First here:

reOrder.Copy After:=Sheets(1)
ActiveSheet.Name = "Original"
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:
Range("B4").Value = Mid(Range("A4"),14,11)


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
04-19-2007, 06:45 AM
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
04-19-2007, 07:08 AM
Also, many places I add many column headings, can I add them all with an array? and if I can would it be quicker?

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"



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

Djblois
04-19-2007, 08:23 AM
I'm not sure about this one. Anyone else?


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


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:

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

Bob Phillips
04-19-2007, 08:42 AM
That is because you have used A1 notation in an R1C1 formula.

Djblois
04-19-2007, 09:35 AM
THis line of code works:



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


THis one doesn't:



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

Bob Phillips
04-19-2007, 09:45 AM
It is not a string, you can't issue VLOOKUP from VBA.

Djblois
04-19-2007, 11:30 AM
So, I can't use that code???

Bob Phillips
04-19-2007, 11:36 AM
Use the one that works, ie with a properly formed string.

mdmackillop
04-19-2007, 12:26 PM
Rather than VLookup, why not use find.

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

Djblois
04-19-2007, 12:30 PM
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
04-19-2007, 12:56 PM
No idea if it's quicker.
For Each cel In Range("E2:E" & finalRow)
cel = Rng.Find(cel.Offset(, -1), , , xlWhole).Offset(, 1)
Next