Macro

[Steved]

Hello from Steved

The macro below finds " TOTAL SHIFT HOURS"
It finds the first and changes to City, then
looks for the next Range("A975").Select
and changes it to Roskill and so no until it does all 9
cities, The problem I have is that Range("A975").Select
gives a range value, next week for an example it might be
Range("A857").Select, which leads to my situation, is
their a work around so that it looks for the first
" TOTAL SHIFT HOURS" no matter where it is in Column
A as this is the only column it will find it in

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
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.FormulaR1C1 = "Papakura"
Range("A1992").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.FormulaR1C1 = "Wiri"
Range("A2679").Select

 

[David Coates]

Hi,
I have just completed some simillar coding which finds a
value in column a and returns a value from and adjacent
column into another cell. Thing is though I can't quite
what the relationship is between the range values for
Roskill, Papakura, and Wiri. It may be that you need to
use the .cells(r,c) reference method and then use
activecell.offset(r?,0) for the same column.

Judging by the cities you must be a NZ'r. If so, and the
above doesn't help, I may be able to assist over the phone
if you like. Just reply and we'll sort out a means of
contacting each other.

Regards

DavidC

 

[Steved]

Hello David From Steved

Yes you are spot on This is NZ

I create a monthly report for my employer
I use information from another report to create my
reporting. To make things clearer is that I look after 9
Cites hence City, Roskill, Papakura and so on.
What I need to do is extract the summary information on
this report, so how I have attempted to go about it is
simply build a macro to find the row with the value I
need for my report, so in the case of City it could be in
Col A:A Row 956 as an example but when I goto it in
future months it might be Col A:A Row 1001, so the
question is How to I write a macro to find the first
" TOTAL SHIFT HOURS" replace it with City then go
and find the next " TOTAL SHIFT HOURS" and Replace
that with Roskill until I done with all nine cities.

The problem lies with the statement Range("A975").Select
So i'm thinking how to I write the macro to reflect, Range
("any row").Select.

In short I have 9 rows which have " TOTAL SHIFT
HOURS"
I replace " TOTAL SHIFT HOURS" with City find the
second " TOTAL SHIFT HOURS" replace that with
Roskill it finds the next " TOTAL SHIFT HOURS" it
replaces that with Papakura and so on until it finds and
replaces all 9 " TOTAL SHIFT HOURS"
The row with the value i need in each city is Col A to
Col H

Sorry it is a bit long but hoping you might be able to
give me a solution.
the row would look like this
Col A B C D E F G H
City 123.45 445.37 447.47 447.47 447.47 447.47 247.21
Roskill 146.33 736.22 736.22 736.22 736.22 736.22 321.37
and so to 9 rows.

The above represents Sun to Sat B=Sun to H=Sat

Hope this give you something to work with, as this report
I extract from has over 6,000 rows of information but I
only require 9 rows of information to complete my report.

Thank's Dave for your thoughts.

 

[David Coates]

Hi try this as an approach. Incidentally i work in
Hamilton for a company called Progen if you need to
discuss anything.

ActiveSheet.Range("A4").Select

With Worksheets("Name of your sheet").Range
("A1:A3500") 'where A1:A3500 is the range of cells
containing the information you are searching. Another
option is to find the first and last cell in the column
and assign a variable to each and reference the range
as .range(.cells(firstrow, column),.cells
(lastrow,colum)). Use selection.end(xldown).select to
find the last row in one of the columns where there is
data in every cell in the column you choose. Assign a
variable to the row number using the following code:
variable =activecell.row

Set c = .Find(wo, LookIn:=xlValues,
lookat:=xlWhole)

If Not c Is Nothing Then

firstaddress = c.Address 'This
assigns the address for the first instance found to a
variable. This variable is then used to select the cell
and then offset it to the required column.

Range(firstaddress).Select

Range(firstaddress).Offset(0,
3).Select 'This statement offsets over to the column
where you want to retrieve the first set of data.

Assigned variable =
ActiveCell.Value

Worksheets
(shtname).Select 'Selects the sheet where you want to
place the data.
Cells(row, column).Value =
assigned variable