Convert vlookup function to macro

[abberyfarm]

Hi there,

I am hoping somebody here could help me out with a macro to do a task that I have.

I use the VLOOKUP function below to do the task, but I have to do this repetitively and a macro would be very handy.

 c2 =VLOOKUP(A2,Sheet2!$A$1:$C$18,3,TRUE)

In sheet 1, I have time values (Col A) and speed values (Col B).

In sheet 2, I have a start time (Col A), an end time (Col B) and in column C either numbers 1 and 2 which indicate a particular type of data.

Here is an example of sheet 1

      Time [sec]	 Speed
14/04/2011 07:40:35	 0
14/04/2011 07:40:36	 1
14/04/2011 07:40:37	 3
14/04/2011 07:40:38	 4
14/04/2011 07:40:39	 5
14/04/2011 07:40:40	 7
14/04/2011 07:40:41	 8
14/04/2011 07:40:42	 9
14/04/2011 07:40:43	 10
14/04/2011 07:40:44	 10
14/04/2011 07:40:45	 11
14/04/2011 07:40:46	 12
14/04/2011 07:40:47	 12
14/04/2011 07:40:48	 13
14/04/2011 07:40:49	 14
14/04/2011 07:40:50	 15
14/04/2011 07:40:51	 3.8
14/04/2011 07:40:52	 7.5

And of sheet 2

Start Time [sec]	   End Time [sec]       Type
14/04/2011 07:40:35	14/04/2011 07:40:39	 1
14/04/2011 07:40:40	14/04/2011 07:40:46	 2
14/04/2011 07:40:47	14/04/2011 07:42:07	 1
14/04/2011 07:42:08	14/04/2011 07:42:38	 1
14/04/2011 07:42:39	14/04/2011 07:43:09	 2
14/04/2011 07:43:10	14/04/2011 07:43:40	 2
14/04/2011 07:43:41	14/04/2011 07:44:11	 2
14/04/2011 07:44:12	14/04/2011 07:44:42	 2
14/04/2011 07:44:43	14/04/2011 07:45:13	 1
14/04/2011 07:45:14	14/04/2011 07:45:44	 1
14/04/2011 07:45:45	14/04/2011 07:46:15	 2
14/04/2011 07:46:16	14/04/2011 07:46:46	 1
14/04/2011 07:46:47	14/04/2011 07:47:17	 2
14/04/2011 07:47:18	14/04/2011 07:47:48	 2
14/04/2011 07:47:49	14/04/2011 07:48:19	 1
14/04/2011 07:48:20	14/04/2011 07:48:50	 1
14/04/2011 07:48:51	14/04/2011 07:49:21	 1

The Vlookup function does the following:

In sheet 2, it checks the value in each row of column C. If the value equals 1, then it goes back to sheet 1 and puts the number one in in every row in column c between the two times. .

So sheet one looks like this after

      Time [sec]	 Speed
14/04/2011 07:40:35	 0       1
14/04/2011 07:40:36	 1       1 
14/04/2011 07:40:37	 3       1
14/04/2011 07:40:38	 4       1
14/04/2011 07:40:39	 5       1
14/04/2011 07:40:40	 7       2
14/04/2011 07:40:41	 8       2
14/04/2011 07:40:42	 9       2
14/04/2011 07:40:43	 10      2
14/04/2011 07:40:44	 10      2
14/04/2011 07:40:45	 11      2
14/04/2011 07:40:46	 12      2
14/04/2011 07:40:47	 12      1
14/04/2011 07:40:48	 13      1
14/04/2011 07:40:49	 14      1
14/04/2011 07:40:50	 15      1
14/04/2011 07:40:51	 3.8     1
14/04/2011 07:40:52	 7.5     1

Thank you in advance

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] WS1 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] WS2 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] TableRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastRow2 [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] WS1 = Worksheets("Sheet1")
    
    [color=darkblue]Set[/color] WS2 = Worksheets("Sheet2")
    
    [color=darkblue]With[/color] WS2
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
        [color=darkblue]Set[/color] TableRng = .Range(.Cells(2, 1), .Cells(LastRow2, 3))
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] WS1
        LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range(.Cells(2, 3), .Cells(LastRow1, 3)).Formula = "=VLOOKUP(A2," & TableRng.Address(, , , [color=darkblue]True[/color]) & ",3,TRUE)"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[abberyfarm]

Hello,

Thank you works perfect!

 

[Domenic]

You're very welcome! And thanks for the feedback!