Range("A975").Select

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

I import a CSV file. I want the macro to find "TOTAL
SHIFT HOURS" 9 times. The first it finds replace it with
the name City then go and find the second subsequent and
replace it with Roskill until it has found all 9 and
replaced it with what I give each one. Okay I have made
myself a macro that can do this as you can see a part
example below. The problem is this Range("A975").Select
is right cell for this month but next month it might be
in cell Range("A977").Select. How to I write the script
to tell the macro to find the subsequent subsequent
bearing in mind this a CSV file and in Column A:975 has 4
spaces before "TOTAL SHIFT HOURS" as shown ie Cells.Find
(What:=" TOTAL SHIFT HOURS",

Thankyou.

Cells.Find(What:=" TOTAL SHIFT HOURS",
After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart,
SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False).Activate
ActiveCell.FormulaR1C1 = "City"
Range("A975").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.FormulaR1C1 = "Roskill"
Range("A1722").Select
 
One way:

Const sFIND As String = "TOTAL SHIFT HOURS"
Dim vArr As Variant
Dim rFound As Range
Dim nCount As Long

vArr = Array("City", "Roskill", "etc")
nCount = 0
Set rFound = Cells.Find( _
What:=sFIND, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
Do While Not rFound Is Nothing And nCount <= UBound(vArr)
rFound.Value = vArr(nCount)
nCount = nCount + 1
Set rFound = Cells.FindNext(after:=rFound)
Loop

change the Array(...) to your nine values

Note that it's almost never necessary (or even a good idea) to Activate
or select ranges. Working with the range object directly makes your code
smaller, faster, and IMHO, easier to maintain.
 
Thankyou JE
-----Original Message-----
One way:

Const sFIND As String = "TOTAL SHIFT HOURS"
Dim vArr As Variant
Dim rFound As Range
Dim nCount As Long

vArr = Array("City", "Roskill", "etc")
nCount = 0
Set rFound = Cells.Find( _
What:=sFIND, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
Do While Not rFound Is Nothing And nCount <= UBound (vArr)
rFound.Value = vArr(nCount)
nCount = nCount + 1
Set rFound = Cells.FindNext(after:=rFound)
Loop

change the Array(...) to your nine values

Note that it's almost never necessary (or even a good idea) to Activate
or select ranges. Working with the range object directly makes your code
smaller, faster, and IMHO, easier to maintain.


.
Click to expand...
 
Back
Top