R
RobertM
I have a spreadsheet that uses the vlookup option. I need to copy values from
an AS400 system and paste them into the spreadsheet. The values range from 1
to 5 digits with blank spaces at the front of the numbers. I'm trying to
write a macro that will (1) paste the values in as text, (2) remove the
leading blank spaces and, (3) mimick the action of clicking on each cell
individually to kick in the vlookup option. Here's the code I'm fighting
with. When I do this the cells go back to zero. Granted I can see the problem
exists in "activecell", but I'm not a programmer and have little to no idea
what I'm doing. Any help is greatly appreciated. Below is a section of the
code:
Range("A20:A31").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A20").Select
ActiveCell.FormulaR1C1 = "0"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
an AS400 system and paste them into the spreadsheet. The values range from 1
to 5 digits with blank spaces at the front of the numbers. I'm trying to
write a macro that will (1) paste the values in as text, (2) remove the
leading blank spaces and, (3) mimick the action of clicking on each cell
individually to kick in the vlookup option. Here's the code I'm fighting
with. When I do this the cells go back to zero. Granted I can see the problem
exists in "activecell", but I'm not a programmer and have little to no idea
what I'm doing. Any help is greatly appreciated. Below is a section of the
code:
Range("A20:A31").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A20").Select
ActiveCell.FormulaR1C1 = "0"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With