Combining code to make smaller

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]

Quote:

---

Originally Posted by Djblois

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?

Quote:

---

Originally Posted by Djblois

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]

Quote:

---

Originally Posted by Djblois

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?

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.

Quote:

---

Originally Posted by Djblois

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]

Quote:

---

Originally Posted by feathers212

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]

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

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]

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

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

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

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]

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.

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]