Excel Macro Assistance [Solved/Closed]

Report
Posts
6
Registration date
Wednesday December 15, 2010
Status
Member
Last seen
January 2, 2011
-
 Helper -
Hello,
I have a macro running a process of performing a couple of formulas in a workbook that uses VLookup.

Every week I have to re-edit the range so that the number of rows are enough for the amount of data that I am using.

Is there a way to setup the macro so that it uses the range of another column to the last line of data instead of modifying the range every-time I run the code?

Sheets("QA").Activate
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = "Tester"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Previous Week'!C[-4]:C[-1],4,FALSE)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H8000"), Type:=xlFillDefault
Range("H2:H8000").Select
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

If there is a better way of doing this please let me know, any suggestions would be great. Thanks.


4 replies

This code assumes there are no blank spaces in column D where you are using the vlookup reference. So, when it is using the Do Loop it will fill the formula in column H until there is no more data in column D. If the length of the data in the QA sheet needs to match the length of the Previous Week sheet, then the Do Loop must be changed to reference the Previous Week sheet or the code will not run long enough to capture the data from the Previous Week sheet.

Hope this helps!


Set q = Sheets("QA")
Dim i
i = 2

q.Activate
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1") = "Tester"

Do Until q.Range("D" & i) = ""

q.Range("H" & i) = "=vlookup(D" & i & ",'Previous Week'!D:G,4,false)"

i = i + 1

Loop

Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("H1").Select
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
6
Registration date
Wednesday December 15, 2010
Status
Member
Last seen
January 2, 2011

Ill have to try this out. The data on the QA sheet will always have more than the previous week.
Posts
6
Registration date
Wednesday December 15, 2010
Status
Member
Last seen
January 2, 2011

Thanks Helper,
This code works great. I'm using the same format you have here and attempting to TRIM and Concatenate columns.


the TRIM format is working but not the concatenate..


q.Range("D" & i) = "Concatenate(C" & i & "," ", B" & i & ")

can you help?
Try this instead.

q.Range("D" & i) = "=Concatenate(C" & i & ", B" & i & ")"
Posts
6
Registration date
Wednesday December 15, 2010
Status
Member
Last seen
January 2, 2011

that didnt work for me. Here is the code that I am using for the process.

Sub Student_Name_Test()
'
' Student_Name_Test Macro
'

'
Set q = Sheets("Tester")
Dim i
i = 2

q.Activate
Columns("D:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1") = "Last Name TRIM"
Range("E1") = "First Name TRIM"

Do Until q.Range("J" & i) = ""

q.Range("D" & i) = "=TRIM(B" & i & ")"
q.Range("E" & i) = "=TRIM(C" & i & ")"

i = i + 1

Loop
Columns("D:E").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("D:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1") = "First Last Name"

Do Until q.Range("B" & i) = ""

q.Range("D" & i) = "Concatenate(C" & i & ", B" & i & ")"

i = i + 1

Loop
Columns("D:D").Select
End Sub


what am I missing here?
Two things....1) when using the variable "i" multiple times throughout the code, you need to re-declare the variable before the next Do Loop. So, before the next Do Loop in which you are referencing column B, make i equal to 2. 2) The Concatenate formula needs to be changed to the way I have it below. It is missing an = at the beginning and the quote marks are not in the correct places.

i = 2
Do Until q.Range("B" & i) = ""

q.Range("D" & i) = "=Concatenate(C" & i & ", B" & i & ")"

i = i + 1

Loop
Columns("D:D").Select
Posts
6
Registration date
Wednesday December 15, 2010
Status
Member
Last seen
January 2, 2011

Great, that concatenate worked, but excel isnt putting a space between the merged cells.
You have to adjust the Concatenate formula to add the space.
Use this adjusted line of code.

q.Range("D" & i) = "=Concatenate(C" & i & ", "" "",B" & i & ")"