Previous Topic Next Topic

Excel Macro Assistance

Solved/Closed
dhunter Posts 6 Registration date Wednesday December 15, 2010 Status Member Last seen January 2, 2011 - Dec 29, 2010 at 11:01 PM
 Helper - Jan 2, 2011 at 06:54 PM
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.


Related:

4 responses

Answer 1 / 4
Helper
Dec 30, 2010 at 08:04 AM
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
Answer 2 / 4
dhunter Posts 6 Registration date Wednesday December 15, 2010 Status Member Last seen January 2, 2011
Dec 30, 2010 at 08:14 AM
Ill have to try this out. The data on the QA sheet will always have more than the previous week.
0
Answer 3 / 4
dhunter Posts 6 Registration date Wednesday December 15, 2010 Status Member Last seen January 2, 2011
Jan 2, 2011 at 02:37 AM
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?
0
Helper
Jan 2, 2011 at 09:07 AM
Try this instead.

q.Range("D" & i) = "=Concatenate(C" & i & ", B" & i & ")"
0
dhunter Posts 6 Registration date Wednesday December 15, 2010 Status Member Last seen January 2, 2011
Jan 2, 2011 at 12:27 PM
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?
0
Helper
Jan 2, 2011 at 02:10 PM
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
0
Answer 4 / 4
dhunter Posts 6 Registration date Wednesday December 15, 2010 Status Member Last seen January 2, 2011
Jan 2, 2011 at 05:11 PM
Great, that concatenate worked, but excel isnt putting a space between the merged cells.
0
Helper
Jan 2, 2011 at 06:54 PM
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 & ")"
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses