Range query

HI all,
Am using the code below to operate a combobox, but would like it to
read the information from the top line of the worksheet, currently is
reading down

Any help with this would be appreciated,


Private Sub userform_Activate()
Dim iLastRow As Long
Dim i As Long
    With Worksheets("Work Data")
        iLastRow = .Cells(.Rows.Count, "1").End(xlUp).Row
        Me.ComboBox1.Clear
        For i = E To iLastRow
            Me.ComboBox1.AddItem .Cells(i, "1").Value
        Next i
    End With
End Sub


HYCH

Steve
0
12/6/2007 5:34:16 PM
excel 39879 articles. 2 followers. Follow

5 Replies
642 Views

Similar Articles

[PageSpeed] 40

I'm not sure I understand, but maybe...

Change this:
For i = E To iLastRow
to
For i = iLastRow to E step -1

(I'm not sure what E is, either.)

Steve wrote:
> 
> HI all,
> Am using the code below to operate a combobox, but would like it to
> read the information from the top line of the worksheet, currently is
> reading down
> 
> Any help with this would be appreciated,
> 
> Private Sub userform_Activate()
> Dim iLastRow As Long
> Dim i As Long
>     With Worksheets("Work Data")
>         iLastRow = .Cells(.Rows.Count, "1").End(xlUp).Row
>         Me.ComboBox1.Clear
>         For i = E To iLastRow
>             Me.ComboBox1.AddItem .Cells(i, "1").Value
>         Next i
>     End With
> End Sub
> 
> HYCH
> 
> Steve

-- 

Dave Peterson
0
petersod (12004)
12/6/2007 7:35:58 PM
On 6 Dec, 19:35, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I'm not sure I understand, but maybe...
>
> Change this:
> For i = E To iLastRow
> to
> For i = iLastRow to E step -1
>
> (I'm not sure what E is, either.)
>
>
>
>
>
> Steve wrote:
>
> > HI all,
> > Am using the code below to operate a combobox, but would like it to
> > read the information from the top line of the worksheet, currently is
> > reading down
>
> > Any help with this would be appreciated,
>
> > Private Sub userform_Activate()
> > Dim iLastRow As Long
> > Dim i As Long
> >     With Worksheets("Work Data")
> >         iLastRow = .Cells(.Rows.Count, "1").End(xlUp).Row
> >         Me.ComboBox1.Clear
> >         For i = E To iLastRow
> >             Me.ComboBox1.AddItem .Cells(i, "1").Value
> >         Next i
> >     End With
> > End Sub
>
> > HYCH
>
> > Steve
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

sorry dave i was trying to adjust the code i had to read across
columns rather than reading down rows.

the original code had that as: For i = 1 To iLastRow

i expected that the no indicated row numbers where as columns are
usually represnted by Letters,

obviously not the right method lol.

All i am looking for is my combobox to read accross the columns rather
than down the rows

0
12/6/2007 7:44:25 PM
dim lastCol as long
dim i as long
With worksheets("work data")
  'I used row 1
  lastcol = .cells(1, .columns.count).end(xltoleft).column
  Me.ComboBox1.Clear
  for i = 1 to lastcol
      Me.ComboBox1.AddItem .Cells(1, icol).Value
      ....
  

Steve wrote:
> 
> On 6 Dec, 19:35, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > I'm not sure I understand, but maybe...
> >
> > Change this:
> > For i = E To iLastRow
> > to
> > For i = iLastRow to E step -1
> >
> > (I'm not sure what E is, either.)
> >
> >
> >
> >
> >
> > Steve wrote:
> >
> > > HI all,
> > > Am using the code below to operate a combobox, but would like it to
> > > read the information from the top line of the worksheet, currently is
> > > reading down
> >
> > > Any help with this would be appreciated,
> >
> > > Private Sub userform_Activate()
> > > Dim iLastRow As Long
> > > Dim i As Long
> > >     With Worksheets("Work Data")
> > >         iLastRow = .Cells(.Rows.Count, "1").End(xlUp).Row
> > >         Me.ComboBox1.Clear
> > >         For i = E To iLastRow
> > >             Me.ComboBox1.AddItem .Cells(i, "1").Value
> > >         Next i
> > >     End With
> > > End Sub
> >
> > > HYCH
> >
> > > Steve
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
> 
> sorry dave i was trying to adjust the code i had to read across
> columns rather than reading down rows.
> 
> the original code had that as: For i = 1 To iLastRow
> 
> i expected that the no indicated row numbers where as columns are
> usually represnted by Letters,
> 
> obviously not the right method lol.
> 
> All i am looking for is my combobox to read accross the columns rather
> than down the rows

-- 

Dave Peterson
0
petersod (12004)
12/6/2007 7:49:24 PM
On 6 Dec, 19:49, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> dim lastCol as long
> dim i as long
> With worksheets("work data")
>   'I used row 1
>   lastcol = .cells(1, .columns.count).end(xltoleft).column
>   Me.ComboBox1.Clear
>   for i = 1 to lastcol
>       Me.ComboBox1.AddItem .Cells(1, icol).Value
>       ....
>
>
>
>
>
> Steve wrote:
>
> > On 6 Dec, 19:35, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > I'm not sure I understand, but maybe...
>
> > > Change this:
> > > For i = E To iLastRow
> > > to
> > > For i = iLastRow to E step -1
>
> > > (I'm not sure what E is, either.)
>
> > > Steve wrote:
>
> > > > HI all,
> > > > Am using the code below to operate a combobox, but would like it to
> > > > read the information from the top line of the worksheet, currently is
> > > > reading down
>
> > > > Any help with this would be appreciated,
>
> > > > Private Sub userform_Activate()
> > > > Dim iLastRow As Long
> > > > Dim i As Long
> > > >     With Worksheets("Work Data")
> > > >         iLastRow = .Cells(.Rows.Count, "1").End(xlUp).Row
> > > >         Me.ComboBox1.Clear
> > > >         For i = E To iLastRow
> > > >             Me.ComboBox1.AddItem .Cells(i, "1").Value
> > > >         Next i
> > > >     End With
> > > > End Sub
>
> > > > HYCH
>
> > > > Steve
>
> > > --
>
> > > Dave Peterson- Hide quoted text -
>
> > > - Show quoted text -
>
> > sorry dave i was trying to adjust the code i had to read across
> > columns rather than reading down rows.
>
> > the original code had that as: For i = 1 To iLastRow
>
> > i expected that the no indicated row numbers where as columns are
> > usually represnted by Letters,
>
> > obviously not the right method lol.
>
> > All i am looking for is my combobox to read accross the columns rather
> > than down the rows
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave,

Just knew you would have the answer as soon as i made myself clear
lol, again thanks :)


Steve
0
12/6/2007 7:52:44 PM
I think a lot of people were confused with your question <vbg>.  Your original
post had gone a couple of hours without a response.  That usually means that
there's confusion on at least one end <bg>.

Steve wrote:
<<snipped>>
> 
> Dave,
> 
> Just knew you would have the answer as soon as i made myself clear
> lol, again thanks :)
> 
> Steve

-- 

Dave Peterson
0
petersod (12004)
12/6/2007 9:22:40 PM
Reply:

Similar Artilces:

IIf Statement in Query Criteria 12-11-09
I have the following code in a query: SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, qryPointsWithDropOffDates.POINTVALUE FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.LASTINCIDENTD...

How to return a range of values in a drop-down.
Is there a way to have Excel return a range of values in a drop down in one cell based on input from another cell. As an example: Cell A1 has the text "PVC Pipe" I want cell A2 to get input from A1, read through a table, and return the corresponding values (sizes) of PVC pipe. The results in A2 would be the following in a drop down: 4" 6" 8" If cell A1 contained the text "Metal Pipe" it would return values (sizes) of metal pipe from a table and not display the sizes of PVC pipe. 10" 12" 15" The bottom line and...

Running a query against a password protected spreadsheet
I used to be able to run an access query against a linked excel spreadsheet and update information in the spreadsheet. The spreadsheet is now password protected, and although I can run the query against it OK, I can no longer update data in it. Does anyone have any solution to this. Regards Steve What version of Access and how long ago were you able to do this? The reason that I ask is that Microsoft lost a patent lawsuit a few years ago on updating linked Excel worksheets from Access. I believe that they blocked this ability from Access 2002 and newer. http://support...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

Out of Range
Hello, This is my VBA macro. I'm getting a "Subcription out of range #9" error message on: Windows(fname).Activate Can someone help fix it, if possible? Regards, fname = Application.GetOpenFilename Workbooks.Open filename:=fname Windows(fname).Activate Hi Jeff, Drop the line: > Windows(fname).Activate The opened workbook is the active workbook. --- Regards, Norman "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:E599F406-0915-4F63-9244-822FA00994CE@microsoft.com... > Hello, > This is my VBA macro. I'm getting a "Subcripti...

Unable to delete cell range.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel New to excel and following a video tutorial. I am using auto-fill to create a range of numbers. The cells are selected and I try to delete all the content in the range by pressing the delete button per the tutorial but only the first cell in the range is deleted. Please help a newbie. Are you using a laptop or a condensed keyboard by any chance? If so, the key labeled 'delete' is the equivalent of 'backspace' & does delete only the content of the active cell in the range. You need to hold ...

Summing a range within a range
I need the formula that will sum a range within a range. Example: the primary range consists of the following numbers: 1,2,3,4,5,6,7,8,9,10 and I need to know how many numbers are >3 and <8. First I need to know the sum ot the qualifying numbers; second I need to know how many items there are. Stumped and exhausted...anyone's help will be greatly appreciated! -- Need to Know Try these... For the SUM: =SUMIF(A1:A10,">3")-SUMIF(A1:A10,">=8") For the COUNT: =COUNTIF(A1:A10,">3")-COUNTIF(A1:A10,">=8") -- ...

Querying for Extended ASCII Characters
I have an imported flat data file that has extended ASCII characters (i.e. 189, 239 and 191) in a Customer Name field. The extended characters are not always in the same location of the Customer Name field and are not always the same extended ASCII characters. My question is there a simple way to query for extended ASCII character (>127) in Access to identify such records and then clean them out of the name record? Example: FName ===== Joe∩┐╜ B∩┐╜ill etc... THANKS IN ADVANCE!! You can use a Replace function. Replace([Customer Name], "┐", "&...

Add to a query Expression!
Overdue: IIf((DateDiff("d",[MyDate],Date()) Between 30 And 60) And ([Payable]>1),"1 Month",Null) How would I add theses 3 expressions into the expression above.............Thanks for any Help.........Bob Between 60 And 90) And ([Payable]>1),"2 Months",Null Between 90 And 120) And ([Payable]>1),"3 Months",Null Between 120 And 2000) And ([Payable]>1),"3 Months Plus",Null Hi Bob I find the easiest way to do this is to build the expression one component at a time. I use Editpad lite which is like notepad. You can use a tes...

Percentage on x axis and period range on y axis
Hi I'm trying to plot the length of time staff have worked for a company on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a seperate column that has converted the months worked into each of these catagories. I then would like to show on the x axis the percentage of staff that fall into each period. I would certainly appreciate any assistance! Wayne Wayne Beasley wrote: > Hi > > I'm trying to plot the length of time staff have worked for a company > on the y axis in ranges i.e 0-6 months, 6-12 months etc. I have a > seperate column that has converted t...

Query Help. 11-30-09
--SQL 2005. I need to return CompanyName only once and all the FullName associates with it. Below is the Business rule. Thank you so much in advance. IF OBJECT_ID('Tempdb.dbo.#Test', 'u') IS NOT NULL DROP TABLE #Test GO CREATE TABLE #Test ( CompId INT NULL, CompanyName VARCHAR(30) NULL, FullName VARCHAR(30) NULL ) GO INSERT INTO #Test VALUES(10, 'Sears', 'John Smith') INSERT INTO #Test VALUES(10, 'Sears', 'Lisa Smith') INSERT INTO #Test VALUES(11, 'Target', 'Peter Smith'...

Display range name when a cell within the range is selected
How to display the name of a range when a single cell within that named range is selected. Kathy one way: This displays the named range(s) that the cell belongs to, if any, in the Status bar. Put this in the ThisWorkbook code module of your workbook. Private Sub Workbook_SheetSelectionChange( _ ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim sRanges As String Dim nmName As Name Dim bInRng As Boolean On Error Resume Next For Each nmName In ThisWorkbook.Names Debug.Print nmName.Name, nmName.Re...

Data Range
Hi there, I have searched through this forum cannot find a solution to what I need so here goes (or perhaps I have missed it). I have a workbook containing 11 worksheets - one for each country with the first worksheet containing a set of standard charts which are reused for each country. I am currently manually amending the data range and selecting the worksheet and highlighting the data for each country (so have to do this 10 times). The data is in the same location on each country worksheet so really the only amendment is the worksheet name. How do you recommend that I speed up t...

Same query: Subform 0, combobox 1, wins by KO
Hello :) I have a table I want to modify but after it's filtered by an ID number. I have created a query that works great and added a listbox that displays the IDs and when I select the ID it requeries the query. Because I want the users to be happy, I have created a form (where the listbox is), and a subfom object that has created a subform where my query is ran. I then have a nice form with a datasheet display underneath to tick the checkboxes I need. BUT, the problem is that the subform displays the first ID correctly - but not the other ones. I have tried to link the WHERE to ...

How do you point to a named range in linked workbooks?
Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Is this what you're looking for...? In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells B2 through K2 with the correct respective links. Hope this helps. -Chad "KG" wrote: > Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I > point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Or, if you don't like to ...

Crosstab Query
I've created a crosstab from a table with data laid out like this: Group Section Title A One John - Manager A One Joe - Analyst A Two Jane - Director B Three Sue - Director B One Bill - Manager C Two Bob - Analyst C Two Tom - Director C Three Jim - President D One Linda - Analyst D Two Lucy - Manager I'd like to end up with a cross tab like this (you may have to use your imagination on layout since couldn't paste a picture) basically trying to get multiple items to display in the value field. Group One Two Three A ...

subtracting a range from another range and getting a range result
I have two ranges of XYZ coordinates. One is named R1 and the other R2. Is there any way in Excell 2002 to bascially say R2-R1=R3 so that R3 is a range the same size as R1 and R2, where each cell in R3 is is subtraction of the corrisponding cells in R1 and R2? Sort of like this: r1= 3,4,5 r2=3,4,5 r3=6,8,10 of course I need to do this on a two dimentional range, there are multiple XYZ points. Are the three values of R1 (3,4,5) in separate cells? Are you adding or subtracting? Please spell out the problem in more detail -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from e...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

Check a range for a value
I have what seems a pretty straightforward question - How, in *one* formula, can I check a range for a value? i.e. Check whether any cell in the range A1:A9000 contains a zero,and return 'Yes'. Any help greatly appreciated......Jason Something like this will do it: =IF(COUNTIF(A1:A9000,0)>0,"Yes","No") and will not count blanks as equivalent to zero. Hope this helps. Pete On Nov 23, 12:41 am, Jay <s...@dummyaddress.spam.com> wrote: > I have what seems a pretty straightforward question - > > How, in *one* formula, can I check a range for a ...

Pivot Table
This is my first post here...I hope that i've chosen the right category.... My problem is similiar to other people's when it comes to consolidating ranges with Pivot Table (sorry for my english...). I've searched the forum but I haven't found either solution or workaround for my problem. My data is divided into 2 sheets, because i need to use more than 255 columns (about 433). It looks like this: col1(ID) col2(Chain store category) col3(address) col4(date) col5-col255 (product's data). One product uses 9 columns like: space on a shelf, price, comments.... Bacause...

Parameter Query and Report
Hi This may already be answered in other threads but I can't get my head round the answers so hope you can help. I have a very simple report in Access 2000 and a simple parameter query which when opened prompts for start date and end date and successfully works when run. I have added the parameter query to the record source of the report but it does not select the records asked for. In summary the parameter query works when you run it on its own but when I open the report (even though the record source is the correct query) it does not work. I simply get the report displaying a...

Still confused about queries to similate vlookup
Normally I would just dump this into excel, but the file that I am dealing with has over 1.5 million rows so excel can't stomach it. Here is what I have done. I have one table (MAIN) that is my main table. It contains all the raw data. I need to convert a city code to a city name. I have another table (CITY) with the conversions from city code to city name. Both tables contain all the city codes. All I want to do is put the city names into my MAIN table next to their corresponding city code (there are about 4000 different cities). I have related the tables relating th...

=IF(Range=Range, Range)
I am trying to use Excel to check if column B = column A then = c. Which works fine for one row, but I would like to sum the results. x x 1 y 1 y 6 x x 2 y 1 y 5 x x 3 y 6 y 4 x x 4 y 3 x 3 x x 5 y 9 x 2 x x 6 y 1 y 1 x x 7 y 8 y 0 x x 8 y 2 x 9 x x 9 y 1 x 8 = ?? = ?? = ?? Thanks, Rob -- hooper222 ------------------------------------------------------------------------ hooper222's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30108 View this thread: http://www.excelforum.com/showthread.php?threadid=497943 Sounds like you can use ...