How do I select a range from 2 ComboBoxes and then offset & select

[Ratigan1970]

Hello all in vba land
i hope i can explain this correctly
I have 2 comboboxs populated with dates in a sheet "ADMIN"
What i would like to happen is combobox 1. i select the first date in a range and combobox 2 select the second date in the range.
then offset to the right by 3 columns and select the 2 cells and everything between
worksheet "Sheet1" is were i have the range b2:b500 with a list of dates


i hope ive explained it ok
many thanks for any help

 

[DanteAmor]

But the combos are in the "ADMIN" sheet and the dates in the "Sheet1" sheet, if you select the dates in the combos, then you are in the "ADMIN" sheet.
Which cells do you want to select? those on the "ADMIN" sheet or those on the "sheet1" sheet?

You could put an image with the expected result, that is, what date you have in combo1, what date you have in combo2 and what range of cells you want to select.

 

[Ratigan1970]

hi DanteAmor the dates on the "sheet1" sheet
worksheet "Sheet1" is were i have the range b2:b500 with a list of dates i would like to find the matching from the comboboxs



i hope this helps

 

[DanteAmor]

But how are you going to view the cells of sheet sheet1, if when you select the dates in the combos you are on sheet "ADMIN"?

You could put an image with the expected result, that is, what date you have in combo1, what date you have in combo2 and what range of cells you want to select.

 

[Ratigan1970]

sorry its part of a wages worksheet
and its part of a bigger macro
i need to select the range to be able to to run the next maco
admin sheet is were all my macros are run from

 

[DanteAmor]

It is not necessary to select the range where you are going to work, I will help you establish the range in a variable (object) and with that you will be able to use the cells of that range without having to select them.

But I want you to put an image where I can see the cells you want in the range.

Simply make an example assuming you select the date January 5 to January 17.

If I'm asking you for the image it's because I don't understand which cells you want in the range.

 

[Ratigan1970]

hi DanteAmor sorry im still new to VBA
and im a little lost. i hope this helps a little more.
sorry for all the confusion

the codes below is what i have to populate the comboboxs

Private Sub Worksheet_Activate()
ComboBox1_Change
ComboBox2_Change
End Sub

Private Sub ComboBox1_Change()
Dim c As Range
  
 
  For Each c In Sheet9.Range("B2:B365")
    ComboBox1.AddItem Format(c.Value, "dd/mMm/yyyy")
  Next
End Sub




Private Sub ComboBox2_Change()
Dim c As Range
  
  
  For Each c In Sheet9.Range("B2:B365")
    ComboBox2.AddItem Format(c.Value, "dd/mmM/yyyy")
  Next

End Sub

i found this code that works for the first combobox
but from then on im lost

Private Sub ComboBox1_Click()
 Dim sh As Worksheet, f As Range
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  Set sh = Sheets("Sheet1")
  Set f = sh.Range("B:B").FIND(ComboBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    sh.Select
    f.Select
  Else
    MsgBox "Does not exist"
  End If
  End Sub

i have 2 pictures below for you too have a look at
what i would like to happen is E2:E5 to be selected

 

[DanteAmor]

Replace all your code for this:

Option Explicit

Dim charging As Boolean

Private Sub ComboBox1_Change()
  If charging = True Then Exit Sub
  Call SelectRange
End Sub

Private Sub ComboBox2_Change()
  If charging = True Then Exit Sub
  Call SelectRange
End Sub

Sub SelectRange()
  Dim sh As Worksheet
  Dim ini As Long, fin As Long
  Dim f As Range
  
  Set sh = Sheets("Sheet1")
  
  If ComboBox1.Value <> "" Then
    Set f = sh.Range("B:B").Find(CDate(ComboBox1.Value), , xlFormulas, xlWhole)
    If Not f Is Nothing Then
      ini = f.Row
      If ComboBox2.Value <> "" Then
        Set f = sh.Range("B:B").Find(CDate(ComboBox2.Value), , xlFormulas, xlWhole)
        If Not f Is Nothing Then
          fin = f.Row
          sh.Select
          sh.Range("E" & ini & ":E" & fin).Select
        End If
      End If
    End If
  End If
End Sub

Private Sub Worksheet_Activate()
  Dim c As Range
  charging = True
  ComboBox1.Clear
  ComboBox2.Clear
  For Each c In Sheet9.Range("B2:B365")
    ComboBox1.AddItem Format(c.Value, "dd/mMm/yyyy")
    ComboBox2.AddItem Format(c.Value, "dd/mMm/yyyy")
  Next
  charging = False
End Sub

 

[Ratigan1970]

OMG !!!!!!!

THANK YOU SO MUCH !!!

the code is spot on

and works like a dream.

thank you so much for your help