Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

vlookup issue

Status
Not open for further replies.

longhair

MIS
Feb 7, 2001
889
US
morning all,
simple issue that escapes me at the moment...
Code:
Dim counter As Integer
Do Until counter = 20
 counter = counter + 1
 astring = ActiveCell.Value
 bstring = ""
 If astring Like ("*B1M") Or astring Like ("*B5M") Then _
 bstring = Mid(astring, 1, (Len(astring) - 4))
 If astring Like ("*B100") Or astring Like ("*B500") Then _
 bstring = Mid(astring, 1, (Len(astring) - 5))
 If bstring > "" Then
  cstring = "=vlookup(" & bstring & ", Sheet1!last, 2, false)"
  ActiveCell.Offset(rowoffset:=0, columnoffset:=6).Activate
  ActiveCell.Value = cstring
  ActiveCell.Offset(rowoffset:=1, columnoffset:=-6).Activate
 Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
 End If
Loop
returns an application defined or object defined error on 'ActiveCell.Value = cstring'
i know i've used similar code before but cannot get the activecell to populate with cstring.
any suggestions?
thanks in advance.

regards,
longhair
 
ActiveCell.FORMULA = cstring



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
xlbo,
thanks for the reply.
already tried that and it returns the same error as using activecell.value
regards,
longhair
 
figured it out.
needed an additional set of '"' chr(34) around bstring since the variable contains spaces.
regards,
longhair
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top