Hello all. I am a web designer/developer and am familiar with programming and variables, but I'm totally new to building macros and VB. This forum has answered many of my questions as I've been learning. So please forgive the potentially naive problem I'm having.
Without boring you with too much detail, basically I'm consolidating tens of thousands of patient records from dozens of separate RTF files into a single Excel spreadsheet so that it can be compared with what already exists. (Please don't question the logic or practicality -- it's what the client wants. )
To sort of explain the idea behind the code snippet below, each of the original RTF files represents all the patients from a particular dentist office. And these files get imported complete with page numbers. So the macro I'm writing looks for "Page 1" and knows it's the beginning of a new location. So then I want to grab the location info and apply it to all the patients it finds in that range.
Everything in the code works as expected except that the variable "LocName" doesn't seem to play nice. It works correctly for the very top of the file, but then I get blanks everywhere else.
And if I replace the line where it's supposed to use the "LocName" variable
with a static string such as
the macro totally works like you'd think it would. So, I'm thinking it must be that the variable doesn't scope correctly somehow.
Anyway, here's the code.
Public Sub AddLocation() Dim lCount As Long Dim rFoundCell As Range Dim LocOffset As Range Dim LocName As String 'Set a fake page 1 after last record for search purposes Range("N13431").Select Selection.End(xlDown).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.Offset(1, 1).Value = "Page 1" 'Reset to top of worksheet Range("A1").Select Set rFoundCell = Range("B1") For lCount = 1 To WorksheetFunction.CountIf(Columns(2), "Page 1") Set rFoundCell = Columns(2).Find(What:="Page 1", AFter:=rFoundCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) rFoundCell.Offset(0, -1).Select Selection.End(xlUp).Select ActiveCell.Offset(2, 1).Select LocName = WorksheetFunction.Trim(ActiveCell.Value) Set LocOffset = ActiveCell.Offset(50, 0) With Worksheets(1).Range(ActiveCell, Cells.Find(What:="Page 1", AFter:=LocOffset, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)) Set c = .Find(What:="Member", AFter:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not c Is Nothing Then FirstAddress = c.Address Do c.Activate c.Offset(0, 9).Select Selection.Value = "LOC Name" ActiveCell.Offset(0, -1).Select Range(Selection.Offset(1, 0), Selection.End(xlDown)).Select Selection.Offset(0, 1).Value = LocName Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> FirstAddress End If End With Next lCount End Sub