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

This assumes:

1. you already have values in column A
2. the values are Text
3. the values may have leading blanks and/or zeros

The macro uses the same approach as we would use without VBA : apply Text To
Columns to the values:

Sub Macro1()
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

--
Gary''s Student - gsnu200759


"RobertM" wrote:

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