VBA - Matching dates to create a selection

[Santaflare]

Hi,
-I’ve been trying to write a macro that will enable a user to select a time slot (between two dates) and combine this with a specified number of hours to complete a task. My goal is to transpose this information to a dataset and then use this to create an interactive chart.

If it would work as intended, a user would be able to make quick changes in the different chart series, as the macro would enable adjustments without any direct editing of the dataset itself.

I’ve thought about offsetting the selection of each macro so that the right columns can be adjusted separately from each other (hence the command buttons), but unfortunately I’ve haven’t been able to write a code that select the range between the dates specified in cell B4 and B6.

Any and all help would be greatly appreciated!

 

[tonyyy]

Santaflare,

The following might get you started...

Sub Subject1_1023813()
Dim v As Variant
Dim col As Long, rowStart As Long, rowEnd As Long

v = Application.Caller
col = ActiveSheet.Rows(14).Find(What:=v, After:=Cells(14, 2), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
rowStart = ActiveSheet.Columns(1).Find(What:=Range("B4").Value, After:=Cells(14, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
rowEnd = ActiveSheet.Columns(1).Find(What:=Range("B6").Value, After:=Cells(14, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    
Range(Cells(rowStart, col), Cells(rowEnd, col)).Select
End Sub

The code is dependent on Form Control Command Buttons, as it utilizes the Application.Caller property to return the button name. The button name (v) is then used to .Find the matching column.

Couldn't tell from the image, so it's assumed the Subject names are in Row 14.

Cheers,

tonyyy

 

[Santaflare]

Thanks that's a great start, and if I could get it working it would really be a huge help!
Unfortunately so far I'm getting a runtime error 91: "Object variable or With block variable not set".

I've tried to adjust the code to the table, and it now looks like this:

Sub Subject1_1023813()
Dim v As Variant
Dim col As Long, rowStart As Long, rowEnd As Long
v = Application.Caller
col = ActiveSheet.Rows(13).Find(What:=v, After:=Cells(13, 2), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
rowStart = ActiveSheet.Columns(1).Find(What:=Range("B4").Value, After:=Cells(13, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
rowEnd = ActiveSheet.Columns(1).Find(What:=Range("B6").Value, After:=Cells(13, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    
Range(Cells(rowStart, col), Cells(rowEnd, col)).Select
End Sub

As you can see in the screen dump below, the table headers are located at row 13, and the data extends from row 14-65.

Since I'm somewhat new to this game , I would gladly take any additional help you might be able to give!
= )

 

[Santaflare]

-Great, I just tinkered a little bit with the code you provided and got it working as intended.
Thanks for the invaluable input - this has really helped me a lot!

 

[tonyyy]

You're welcome. Glad you got it working...

 

[Santaflare]

I’ve now tried to use the following code to create an interface where users would be able to insert new data series in a chart (as mentioned above). However, I’m constantly getting strange results when I’m running the macro several times.

The first few times everything seems to function just fine, but then any data added becomes skewed, and is inserted too far down in the column as well as being added to multiple cells even though I try to just add it to a few. Sometimes the opposite happens as well; and only one cell is filled, even though the chosen series covers several rows.

Could it be something wrong with the macro, or is there something else that might affect the outcome?

Sub AddSeries()
Dim v As Variant
Dim col As Long, rowStart As Long, rowEnd As Long
Range("Y2").Select
Selection.Copy
Sheets("Model2").Select
v = Application.Caller
col = ActiveSheet.Rows(3).Find(What:=v, After:=Cells(3, 2), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
rowStart = ActiveSheet.Columns(1).Find(What:=Range("B2").Value, After:=Cells(3, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
rowEnd = ActiveSheet.Columns(1).Find(What:=Range("D2").Value, After:=Cells(3, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    
Range(Cells(rowStart, col), Cells(rowEnd, col)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Model1").Select
Range("A1").Select
Selection.ClearContents
End Sub

 

[tonyyy]

Could it be something wrong with the macro...

Possibly.

...or is there something else that might affect the outcome?

Possibly.

First question: What does this code snippet do?

Range("Y2").Select
Selection.Copy

It seems it's not pasted anywhere so why copy it at all?

Second question: If you meant to paste that into a cell in another sheet, what are the sheet names? And rather than copy/paste, it might be more efficient to write something like...

Sheets("Model1").Range("D2").Value = Sheets("Model2").Range("Y2").Value

From your screen shots it's difficult to discern which is Model1 and which is Model2, if either. (Here's a tip: Instead of posting images, use the link in my signature to learn how to post your data. This will reveal the sheet names to those trying to help you, as well as save them from tedious typing when trying to recreate your sample.)

That said, you might consider...

...Replacing "ActiveSheet" with the sheet reference; eg, Sheets("Model2")
...Fully qualifying any Cells or Range references; eg, Sheets("Model2").Cells(3,2) or Sheets("Model2").Range("B2") or Sheets("Model2").Range(Sheets("Model2").Cells(rowStart, col), Sheets("Model2").Cells(rowEnd, col)).Select