looping a macro

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
Hi all,

I have created a list of dates in the A column. I have created a list of times in the B column. I.e.

11/10/2006 6
11/10/2006 12
11/10/2006 14
11/10/2006 18
12/10/2006 5
12/10/2006 11
12/10/2006 14


These values may run from Row 1 through to Row 40.

I have in column C and E (respectively) dates and times in the following format:

11/10/2006_____1
______________2
______________3
______________4
______________5
______________6
______________7
______________8
______________9
______________10
______________11
______________12
______________13
______________14
______________15
______________16
______________17
______________18
______________19
______________20
______________21
______________22
______________23
______________24
12/10/2006_____1
______________2
______________3
______________4
______________5
______________6
______________7
______________8
______________9
______________10
______________11
______________12
______________13
______________14
______________15
______________16
______________17
______________18
______________19
______________20
______________21
______________22
______________23
______________24


What I would like to do is for each time and date in columns A and B, enter them in the F column for the corresponding date and time. The output should look like:



11/10/2006_____1
______________2
______________3
______________4
______________5
______________6_____11/10/2006 6
______________7
______________8
______________9
______________10
______________11
______________12_____11/10/2006 12
______________13
______________14_____11/10/2006 14
______________15
______________16
______________17
______________18_____11/10/2006 18
______________19
______________20
______________21
______________22
______________23
______________24
12/10/2006_____1
______________2
______________3
______________4
______________5_____12/10/2006 5
______________6
______________7
______________8
______________9
______________10
______________11_____12/10/2006 11
______________12
______________13
______________14_____12/10/2006 14
______________15
______________16
______________17
______________18
______________19
______________20
______________21
______________22
______________23
______________24


*Note; the cells in the C column are merged, such that if you find the correct date and offest one to the right you will arrive at 1 in the E column.

Any ideas on how to create such a loop?

Thanks

John
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
All,

I have started trying to figure out a macro which is as follows:

Sub Find values()
Dim Bcell As Range
For Each Bcell In Range("AG14:AG40")

Set findit = Range("B16:B239").Find(what:=Bcell.Value, lookat:=xlWhole)
findit.Offset(0, 5).Value = Range("A3").Value

Next Bcell
End Sub

However, everytime I try to run this I get Run Time Error 91
(Object variable with Block variable not set)

The error is in the following line of code:

findit.Offset(0, 5).Value = Range("A3").Value

A3 contains text.

Any ideas how I can fix this problem?

(Basically this macro looks at each cell in a range, and if the value in that cell equalts any value in cells B16:B239 it should spit out whatever is written in cell A3 5 columns across; well that's what I'm trying to do)

Thanks

John
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
John

Why does the 11/10/2006 6 go beside the 11/10/2006____1 entry when all the others seem to match the date / time combination?


Tony
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

John

The code below assumes:
Dates are in the range A1:A7
Times are in the range B1:B7
Data in columns C and E starts in row 1 (but this is not really fixed in those columns)
There will always be a match on the date.

Code:
Sub bbb()
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set findit = Range("C:C").Find(what:=ce)
    findit.Offset(ce.Offset(0, 1) - 1, 3).Value = ce.Value & " " & ce.Offset(0, 1).Value
  Next ce
End Sub


HTH

Tony
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
Thats awesome; thanks Tony.

Now I just have to fiddle around with it becuase I've changed my layout :P

Thanks again
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174

ADVERTISEMENT

Tony,

I est up a blank worksheet and this formula worked fine in the columns that my proper spreadsheet have data in. However, when I transferred this Macro across to my proper spreadsheet Excel keeps telling me error '91' Object Variable not set. This is what the macro now looks like:


Sub bbb()
For Each ce In Range("AG14:AG40" & Cells(Rows.Count, 1).End(xlUp).Row)
Set findit = Range("B:B").Find(what:=ce)
findit.Offset(ce.Offset(0, 1) - 1, 25).Value = ce.Value & " " & ce.Offset(0, 1).Value
Next ce
End Sub

I can't see how suddenly it would not recognise these variables? Could you please enlighten me as to any reasons this may occur (I had this before and the reason was the cell I was referencing I had accidentally deleted :P, but that's not the case atm).

Thanks

John
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
John

Change the for each row to

For Each ce In Range("AG14:AG40")

As you have a specific range, you don't need anything to determine the end of the dynamic range. Also, do you have option explicit set? If so, then you will have to dim the variable ce.


Tony
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
Tony,

Still giving me the same error. Might I ask what ce refers to please? Cell ?

It's having a problem with:

findit.Offset(ce.Offset(0, 1) - 1, 25).Value = ce.Value & " " & ce.Offset(0, 1).Value

However I can't see why there would be a problem herE? ? ?
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
John


ce is a variable that works on each entry in the range AG14:AG40 So for the first run through, it refers to AG14. If you step through the macro then look at the immediate window in debug mode, you can see:

ce.value has the value of AG14
ce.address returns AG14
ce.offset(0,1).value has the value from AH14

If you can't solve it from this, post back with your revised structure and I'll have another look.


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,362
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top