Get Current Row Number Of A Drop Down Box

Dec 23, 2009

I have an application that when user insert a new row, it will place a dropdown box on first column of that row waiting user to make a selection to pupolate other attributes to the rest of columns of the row.

That runs fine if user follow the sequence precisely. That is, user create a row, make a selection, then create yet another new row, make a selection, and so on.

My trouble is that when user created a few rows consecutively without picking a selection from each dropdown first, but instead s/he first created a few rows (say on 21, 22, 23, 34, and 25) and then came back to make a selection from the dropdown box in row 21, the VB code would insist current working/active row number is 25, not 21. This becomes a big problem.

curRow = ActiveCell.Row
curRow = Selection.Row

both show curRow = 25, which are incorrect

Is there any mechanism that when user click on a dropdown, I will be able to get its correct position (row = 21, column = 1, for example) from the engaged dropdown box? In other programming languages "Me" is a reserved object that would give out individual's property, but I am not sure if that exists in Excel macro/VB.

I am new to Excel macro. I am looking for its answer for a few days and unfortunately I haven't found any clue yet.

View 7 Replies


ADVERTISEMENT

Show Current Selection In Drop Down Menu

May 4, 2009

Hi guys i am in assistance of your brilliant brains, i have a working drop down menu which when selected i can select my data range such as below: ...

View 6 Replies View Related

Drop-Down List With Dates Before & After Current Date

Dec 10, 2006

Is there a way to create a drop down list using Data/Validation that will create a list of dates that look ten days back and that look ten days ahead?

View 4 Replies View Related

Counting The Number Of Occurrences Before The Current Week Number?

Jul 10, 2014

I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.

I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.

------ UPDATE

In response to using CountIFS I have also tried-

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.

View 10 Replies View Related

Drop Whole Number And Leave Decimal Part Of Number For Calculations?

Feb 9, 2013

How can I drop the whole number part of a number and leaving only the decimal part of the number. Then multiply the decimal part of the number with a number. Then repeat this in a sequence. The object is to convert Lat and Long decimals to Hr. Min. Sec.

eg. 53.535663 .535663*60=32.13978 .13978*60=8 53 32 8
eg. 113.352640 .352640*60=21.1584 .1584*60=9 113 21 9
eg. 113.306579 .306579*60=18.39474 .39474*60=23 113 18 23

View 1 Replies View Related

Showing Current Page Number And Total Page Number In Entire Workbook?

Nov 14, 2013

In my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".

I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.

View 6 Replies View Related

Sum Everything Upto The Current Row Number

Apr 5, 2009

So, I need to keep adding together all the numbers from cell row 0 upto and including the row number of the cell with the result. The column with the results is to the right of the column with numbers to add. So, if the numbers are positive, to the right of each should be an ever increasing number as a result. In the column with the numbers to add is a function which as a result gives either numbers or blank cells. It sounds simple but it is frustrating me. Why can't I use the function SUMIF with a ROW function inside the condition field? It always gives me 0 as the result. I use excel 2003.

I probably do't understand the SUMIF function but why isn't =SUMIF(A:A;"1>0") equivalent to =SUM(A:A). This too gives the result of 0. Why don't I get an error instead.

View 6 Replies View Related

360 Days :: What Is The Current Number Of Day?

Dec 22, 2009

there are 360 days in a year. is it possible for excel to show what is the current number of day?

eg. today is =350

View 9 Replies View Related

Table Name Or Number For Current Worksheet

Apr 2, 2008

Does anyone know the VB syntax for obtaining a table name or number on an existing worksheet? I'm using Excel 2007 and have a worksheet that is one large table. I have a macro which copies the worksheet to a new sheet, converts the table to a range and then edits out the elements I don't require (my macro won't strip out information I don't require if it's still a table, which is why it's converted to a range). However, every time I copy the sheet, the table number increments by one.

For example: Unitlist is the original table name. When I copy the worksheet, the table on the copied sheet becomes Unitlist1. If I delete the sheet and copy it again, the table becomes Unitlist2 etc. etc. In order to get around this, I need to be able to reference the name of the table on the copied sheet and then use that reference to convert the table to a range. I can convert the table to a range using:

ActiveSheet.ListObjects("Unitlist1").Unlist

However, I need an automated way to obtain the table name. I've attached a copy of my macro for further reference.

Sub CopySheetDeleteData()
Dim c As Range
Dim i As Long
With Application
.Calculation = xlCalculationManual
. ScreenUpdating = False
Sheets("Units").Copy After:=Sheets("Units")
' rename the sheet
Sheets("Units (2)").Name = "ExportUnits"......................

View 2 Replies View Related

Subtract Decimal Number From Current Date

Nov 14, 2006

Need to find out exact date. if I insert 32.5 (32 years 5 months ) in particular cell. my output shld be 10 june 1974 or 10/06/1974. I want to find back date...

View 4 Replies View Related

SaveAs Current Date & Append With Number

Sep 19, 2007

I have saved an Excel file based on today’s date using: ActiveWorkbook.SaveAs "S:MTL_DATA Stock Orders" & "Stock_Order_" & oname & Format(Date, "dd-mm-yy") & ".xls"

If I wish to save a second file in the same day I wish to save as the date with “_1” or date with “_2” for the third file etc. How do I alter the above code?

View 4 Replies View Related

Number Of Days Between Julian Day & Current Dates

May 16, 2008

As I searched I see that JD "Julian Day" started from '4713/01/01' BC, counted 1 noon at noon. and suppose we are now in '2008/05/16', what is the algorithm of finding the days from '4713/01/01' BC through '2008/05/16'?

View 4 Replies View Related

Get Row Number From Drop Down List

Jul 18, 2006

I have a prob. that shouldn't be so hard but I just cant find the syntax how to do it. I have a drop down list with dynamic content, that is, it´s not an array but the contents will change upon what the user is doing with the user form. Now, I need to find out the row number of the cell with the variable in it.
Ex:

Drop down lost contains "Sarah" in A1, "Pete" in A3, "Bob" in A4, "Sarah" in A5.
When the user chooses Pete and push a button I want the number "3". It could be stored in another cell or whatever. Since the contents will change I cannot use an array or a fixed column.

Private Sub knappen_Click()
Range("D2").Value = cmbBox.??????
End Sub

View 5 Replies View Related

Using Drop Down Box To Subtract From Number Based On Yes Or No?

Dec 2, 2011

I created a drop down box with 3 options. Yes, No, and N/A. I want to subtract from a fixed number (8) a certain amount based on the answer. Something like if yes then subtract 1 from 8 and have the final number (7) appear in another cell.

screenshot attached.

[URL]

View 9 Replies View Related

Display Current Code Line Number While Macro Running

Sep 19, 2007

Is it possible to display both the current code line number and the total line count of a macro while it's running?

I was thinking it would be handy for a progress indicator if the ProgressBar max value could be set to the LineCount total of the procedure and each line of code would increment the progressbar as the macro ran.

View 9 Replies View Related

VBA - Add Number To Certain Cells Based On Drop List Value

Jun 30, 2014

I have a numerical value in cell B2 , and a drop list that contains unit names.

I am looking for VBA to see which unit is selected in the drop list, and then add B2's value to that particular unit's total.

See attached workbook : oz_addition_example.xlsx

View 4 Replies View Related

Switch Order Of Last / First Name And Drop Any Number In Text

Feb 19, 2014

I have a first & last name with a number in cell B2 which looks like the following;

939 Smith, Silvia

I would like to drop the numbers and just return 'Silvia Smith'.

I have the following part of the formula to drop the number and it works. =RIGHT(B2,LEN(B2)-FIND(" ",B2)) Need to know how to layer in the other part of the formula to switch first and last name.

View 2 Replies View Related

Textbox That Shows Current Record Number Or Listbox To Show All Records

Apr 14, 2013

I am looking for a text box code that works with a search userform.

Basically, I search using my userform find function and if there are more than one record found I want to be able to either:

1) have the records found appear in a listbox
0r
2) have the first record appear in the userform but a text box will show I am on 1 of X records and when I click a command button, go the next record, which will be 2 of x records and so on...

VB:

Private Sub cmbNext_Click() Dim FirstCl As Range
'first data Entry
Set FirstCl = Range("a2").End(xlDown).Offset(1, 0)

[Code] ....

This is the code for the button that goes to the next record but I am unsure how to relate that a listbox or text box that shows the record number I am on out of the total that there are.

I would also be looking for another button that goes back one record. So i am hoping it's as easy as reversing the code for the next record function.

I am not sure if the listbox that could show all I records and one can just be selected is easier than showing the textbox with the " 1 of X records".

View 2 Replies View Related

Reference A Number Of Cells From Accessing One Drop Down Menu

Feb 11, 2009

I am trying to figure out how to reference a number of cells from accessing one drop down menu. I have a list of products with pricing and logistics. I would like to have the drop down menu in another sheet that will access a specific product and the pricing that goes with it. I know how to do it if i wanted 100 seperate sheets, but I would really love an all-inclusive page that refreshes with the drop down menu. I have a copy of the file if that i hope will be helpful with colour coded cell references.

View 2 Replies View Related

Give A Valor To A Selected Number In A Drop Down List

May 25, 2007

when i selelct a number of a the dropdown list can i get it to show other number in the cell below? example if i select 7:00-15:00 from that list it would show 8:00 in the cell below..

View 9 Replies View Related

Inputting Number Representing Time Spent On Task Under Drop Menu

Dec 8, 2013

Is it possible to create a drop down menu where additional information can be added then tallied up in a separate table?

I would like to create a menu representing "tasks" where an amount of "time" can be designated per menu for my employees to select and fill. I would like this information to then be tallied elsewhere so I can keep track of our efficiency.

I've attached the excel file to give a better idea of what I'm trying to accomplish.

View 9 Replies View Related

Select An Item From Drop Down List And Automatically Fill A Number Of Cells

Nov 15, 2008

I am kind of new to Excel programming and would like to know if there is a solution to my problem. What I need is to have a dropdown list (I know how to make this) then select an item from the drop down, then based on the name i select, Excel automatically fill up the rest of the cells.

For example, from the drop down list I select the company name then Excel automatically type the address, phone number, Fax number and the contact name of the company for me in corresponding cells. Is there a way to do this?

View 3 Replies View Related

Calculate Maximum / Minimum Value Of Current Value Based On Current Value And Previous Value In Data

Jan 6, 2013

I have series of data values like below. I have to find Maximum, Minimum values for each of these values.

9430
9822
10070

[Code].....

View 2 Replies View Related

Calculate Number Of Days Between Two Date Within A Current Month Including End Date

Apr 2, 2009

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

View 9 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

Find Last Number In Row / Add 1 To Current Cell (if Another Cell In That Column Has Text)

May 23, 2014

file storage

1. Look at the "Days so far" section

2. Ignore cell C1

3. All the other cells in that row are shaded blue. Look at these.

4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.

5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.

6. For example, we begin in C1 with a count of 12 days so far - this was manually entered

7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.

8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.

9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.

P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.

View 5 Replies View Related

Formatting Worksheet: Re-adjust The Worksheet To Make It More User Friendly Based On Number Of Rows In Current Sheet

Oct 14, 2008

I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.

View 2 Replies View Related

What Its Called When You Have Drop Down Menus That Correspond With Other Drop Down Me

Nov 22, 2008

what its called when you have drop down menus that correspond with other drop down menus.

View 2 Replies View Related

Populate Drop-Down Based On Another Drop-Down Choice

Oct 25, 2006

Here is what I'm trying to do:

I have 4 sheets:
1 called main
1 called states
1 called counties
1 called people

On "main" I have 1 drop down box and 1 cell:

the drop down lists the states from "states" sheet
the cell, which i would like to have as a drop down is supposed to be listing the counties in the state selected from the 1st drop down. right now it is setting the value to TRUE for testing purposes.

then when the county is selected, it will display the people in that county listed in "people". So far I can only get the 1st drop down to list the states. that's the easy part. i need to getting the counties to list in another drop down based on what state is selected.

View 2 Replies View Related

Populate Drop-Down Based On Another Drop-Down Selection ...

Dec 5, 2007

Im a fairly basic VBA user who has just purchased the code for J-Walk's Enhanced Data Form.

On other threads in this forum I have noticed a lot of people asking how to make the contents of one drop-down box dependant on the selection in another. I am trying to do the same thing but with the criteria section of the EDF form.

Is this possible? What does give me hope is that a simple Autofilter - whilst not as aesthetically pleasing, does the job well.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved