# Find Cell Value Row Number & Use For Column Number

Jun 2, 2008
to update these values via a form in this sheet. I can find the correct row to be edited by entering a value from column A and B. The problem is if I want display the values of that row first and then change it. If I want to change row 10 data how can I bring back the value in ROW 3 AND THE COLUMN VALUE? The next step would be to do the actual update if I want to change ROW 10 to "Ooi" and a sales value of 200?

This is what I have done so far:

Dim myRows As Integer

With Sheets("Mrt")

'Retrieve history information for row

For myRows = 4 To 49

If comboxDay.Text = Range("A" & myRows).Value And textboxdescription.Text = Range("B" & myRows).Value Then

textboxbedrag.Text = Range("C" & myRows).Value

chkBTW_Ja.Value = Range("D" & myRows).Value

txtNota.Text = Range("S" & myRows).Value

End If

Next

End With

Picture attached to show how sheet looks like.

View 9 Replies
ADVERTISEMENT
Oct 14, 2013

I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:

LR = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(LR, 1), Cells(LR, "A")).Select

View 1 Replies
View Related
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
Jul 21, 2007

I am building a Workbook which takes data from SheetA and inserts it into SheetB.

Part of the data is only entered when a positive value exists.

I then do an export from SheetB.

The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.

Dim sFindstring As String

Dim rFindcell As range

Dim iR As Integer

Dim iC As Integer

sFindString = " Find this string in the cell"

'Using cells find the findstring

Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

'OK so look here

iR = 3

'I am trying to pick up the column number

iC = rFindCell.Column

rFindCell throws an object or with block variable not set error. Is there some property that I need to set.

View 3 Replies
View Related
Jan 11, 2014

I am trying to save an excel sheet to .csv format with the following macro:

[Code] ......

The following part of the code needs to be modified so that the commas appear correctly in .csv file even for null values for any given column.

[Code] .....

I have attached the Sample Sheets. Source sheet and the Result sheet. The Result sheet doesn't provide the required result. Some data are missing and unwanted commas have been added. How to correct the logic in the above piece of code.

Attached File :

Result Sheet.csvâ€Ž

Source.xlsmâ€Ž

View 14 Replies
View Related
Nov 20, 2008

i have 2 sheets one called "IS" and the other called "AS" in cell a2 of "IS" is a number that i need to have excel look up in column a of "AS" once it find that number i need it to return the number thats in column e of that row to sheet "IS"column d. summary: a2 of "IS" looked up on sheet "AS" and returns the number in column e to cell d2 of "IS"

View 3 Replies
View Related
Dec 11, 2008

I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!

D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?

View 5 Replies
View Related
Aug 16, 2007

I'm having trouble coming up with a formula that will find the first month with a number in the 'Total' row (row 22) without using an array function or VBA. The months are in row 13 and can vary from 1 to a whole lot. The number of line items, column C, will also vary, but I wouldn't expect that to effect any function (aside from changing the row designation). Here's what I put together using an array function: {=MATCH(TRUE,ISNUMBER(D22:L22),0)}

Again, don't want to use an array function. Other people will be using this and when they mess with it, i don't want them asking me why it won't work anymore.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutM14M15M16M17M18M19M20M21D22E22F22G22H22I22J22K22L22M22=

CDEFGHIJKLM12LineMonths*AROTot13123456789141*********0152**5*6*2**13163**4**3***71740000*****0185*421***5**30196***1***8*9207*4**9**3*16218*********022Total*

8301153711*

75Sheet1*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Aug 20, 2013

I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.

I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.

View 9 Replies
View Related
May 20, 2014

The simple table (attached) represents our problem.

We want the letter in Column-B that corresponds to the last non-zero entry in Column-A. So in the example provided, the answer will be cell B12 which is "L". Also, if there are blanks (rather than zeros) below the last non-zero entry, it still needs to work.

View 11 Replies
View Related
Feb 2, 2009

I have a a spreadsheet with content (text) in one cell pr line. The content can be in the range column 1 to 8. I want to make a formula where I get the number of the column where there is content. I know how I can make this by using nested IF's, but are there any better proposal?

View 3 Replies
View Related
Feb 1, 2013

I have a set series of numbers as follows:

7550-0101

7536-0130

7538-0131

7535-0128

7536-0126

[Code] ....

I would like to disregard the digits before the hyphen (not always four digits) and check only the values of the last set of digits (also not always 4 digits). I would like to search for the greatest suffix used to find the next available number. In my data above the result would be 132.

View 3 Replies
View Related
May 13, 2009

I Have a row witch contains some numbers…..some of them are zero. I want to look: column after column throw this row and find first not zero item. For example:

A B C D E F

0 0 0 12 3 0

Result is “D”

BUT!

U want to make it with standard excel functions, without programming any user functions with VBA! Only Excel…

View 9 Replies
View Related
Jun 14, 2007

I'm trying to do something similar to the Vlookup function (or Index/Match method) but with two or more parameters to match instead of just one. I want to returnn the Row where two columns match the input parameters. Using Vlookup is a problem because it only returns the first one.

Is the standard method to us Vlookup for one parameter, then check the second and if it doesn't match reduce the search field, find the next and check the second again etc? That would work but it would be much simpler if there's a 2 parameter function.

View 3 Replies
View Related
Mar 1, 2012

Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)

I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.

Cell

A1 Number >2000300040005000600070008000900010000

70 2864295717148571000114312861429

84 24236148059971883795610751194

[Code].....

View 2 Replies
View Related
Jan 15, 2007

I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:

=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)

but all I get is hashN/A?

View 14 Replies
View Related
May 28, 2008

I have a database with over 100 products listed across the first row.

Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.

For Example

A B C D E etc.

Products --> X Y Z AA

Proj 1 2 3

Proj 2 1 4 5

Proj 3 2 4

etc.

I want to be able to create a report for any given product.

The report could look like,

Product Z

Proj 1 3

Proj 3 2

So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.

I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.

View 14 Replies
View Related
Jan 19, 2010

Yet some more changes to a badge log, have worked out most of it but am having problem selecting the right row. The problem I'm having is finding the badge number (set in column J) and then finding the row that doesn't have the check out date (column B).

I know this code will find the first entry in the data sheet, I just can't figure out how to set it up to find the badge number that hasn't been checked in yet.

View 13 Replies
View Related
Jul 28, 2006

I want to find the Row Number, not necessarily the cell value, of the last non-blank entry in a Column.

If the address of this row is found, then that could also be useful. I believe there are some simple Excel functions to do this, maybe involving the X1Up feature. I've searched the threads and haven't found a clear answer this.

Below is code that I custom wrote, but its long and tiresome to use.

'The purpose of this sub is to find the last filled row in a column

'Knowing this last row is useful for telling later

' looping operations which row to stop on.

'The logic of this sub is that it will look down a column.........

View 9 Replies
View Related
Jun 3, 2014

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

View 1 Replies
View Related
Jun 4, 2014

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

examples.xlsxâ€Ž

View 1 Replies
View Related
Mar 26, 2014

I need a VBA code to find the nearest biggest number and nearest lowest number between the data of D2 to H2. In the attached file, I have mentioned my required output (Column A and B - blue highlighted)

View 2 Replies
View Related
Nov 1, 2013

I've used the below, for example in Column E, the actual data starts in row 4, so I can't use the full E:E syntax:

=IF(ISERROR(INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000))),

INDEX(Data!E$4:E$65000,MATCH("z",Data!E$4:E$65000)),

INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000)))

The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)

However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.

Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)

View 9 Replies
View Related
Sep 25, 2008

ColumnA Column B ColumnC (Yes/No)

2121 345766

23423 6456546

4234 6456456

7567 64566456

76756 654645

67567 3344534

76575 34534534

756756 45345

7657 534545

756767 4534534

756756 435345

Hello,

What I am trying to do is

Search the number written at ColumnB in ColumnA and if found write YES to column C

if not write No

View 9 Replies
View Related
Aug 9, 2013

I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below

Red

Green

Blue

Orange

Yellow

Result

2

4

3

6

1

[code]...

I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers

View 4 Replies
View Related
Aug 17, 2012

Is is possible for excel to find a number in a cell and display it in a new cell?

A1 = AWS190B

A2 = AGZ160D

A3 = WGZ080D

Is there a command for excel to display 190 in A2?

B1 = 190

B2 = 160

B3 = 080 (or 80 is fine)

View 2 Replies
View Related
Mar 2, 2012

I have data in a column (G) consisting of zero and ones. I would like to count the number of clusters of the number 1 in the data. For example in the data below there are 8 ones. But instead, I need to calculate how many groups of 1s occur. So in the case below the group of 1s = 3.

In terms of what defines a group. Whenever there is a zero either before or after the occurance of a 1 constitues a group, i.e the groups are broken out by zeros.

0

0

0

0

1

1

1

0

0

0

0

0

0

0

0

0

0

0

0

0

1

1

1

1

0

0

0

0

0

0

1

0

0

View 3 Replies
View Related
Feb 6, 2013

I have a number, eg, 1234567 and rounded to prepare the shrink of the size of the number like

= Round(a1, -3). I have, say now, 1234000.

I need to strip "000" from the number and place 1234 to a cell.

View 4 Replies
View Related
Aug 21, 2014

I go negative in one column I want it to show up in the next column as a positive number because when I add my income and subtract my bills and the negative from last month to get the what cash I'll have left it screws up because negative subtract a negative is a positive. So how do i get the sum of say B9 to from -$5.00 to +$5.00 into C4 so when i subtract C4 From my income it wont add to it.

View 1 Replies
View Related
Mar 10, 2014

I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.

View 7 Replies
View Related