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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thats awesome; thanks Tony.

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

Thanks again
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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? ? ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top