Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Userforms & comboboxes

  Asked By: Cameron    Date: Dec 18    Category: MS Office    Views: 847
  

I am working on a userform with a simple set of dates with 5 columns
of data associated with each date. A combobox has been populated with
the column of dates (A4:a19). The user selects a date from the combo
box and a field is populated with the average volume associated with
that date (in this case, column F, a columnoffset of 5). so, the
subroutine takes the user-selected value and interrogates each value
in the range until it finds a match, then collects the associated
value from column F. For some reason, the variable I am using to
increment the row is filling and overflowing. No match is ever found
and I would GREATLY appreciate any comments. I can upload the whole
spreadsheet, if need be. TIA

Private Sub cboWeek_Click()
'======================================
'
'Declare Variables
Dim strWeek As String
Dim rngData As Range
Dim shtWork As Worksheet
Dim introw As Integer
Set shtWork = _
Application.Workbooks("weekly summary.xls").Worksheets("nasdaq")

''Display controls that are hidden
If fraIndex.Visible = False Then
fraIndex.Visible = True
lblVolume.Caption = "Volume:"
lblAvgVol.Visible = True
End If
''Format combobox
cboWeek.Value = Format(cboWeek.Value, "mmm. dd, yyyy")
obtOpen.SetFocus

'Display Average Volume of each Week in the lblAvgVol
Set rngData = _
shtWork.Range("a4").CurrentRegion
strWeek = cboWeek.Value
'locate first occurence of Weekly Date
introw = 2
Do Until rngData.Cells(RowIndex:=introw, columnindex:=1) _
.Value = strWeek
introw = introw + 1
Loop
Do While rngData.Cells(RowIndex:=introw, columnindex:=1) _
.Value = strWeek
lblAvgVol = rngData.Cells(RowIndex:=introw, columnindex:=6).Value
introw = introw + 1
Loop
End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Hollie Hughes     Answered On: Dec 18

I think you are trying to compare a string with a date.

 
Answer #2    Answered By: Jackson Williams     Answered On: Dec 18

The other question I wondered; why useVB to 'load' or 'interrogate' the data?
why not use the SumProduct() function with an average  against the 2 columns (as
arrays)?

For an interesting video 'walk-through', check out DataPigTechnologies.com for a
SumProduct example.

 
Answer #3    Answered By: Ethan Evans     Answered On: Dec 18

Your loop is not finding the strWeek in the Range provided. intRow
should be classed as a Long. However, I think your real issue is with
strWeek.

strWeek is a String. Isn't column  A the date  field?

 
Answer #4    Answered By: Komal Mohammad     Answered On: Dec 18

Thanks to everyone for your input. Of course, my search for a match
was never 'true,' so I did get lost. what I couldn't figure out was
how to get the formats 'the same.' Finally, I put FORMAT in front of
the rngData.Cells& got it to work.

One thing that frustrates me is that there are so many ways to do
things and I find the reference guide hard to use.

 
Didn't find what you were looking for? Find more on Userforms & comboboxes Or get search suggestion and latest updates.




Tagged: