problem with assigning row number

**dschmitt** · 05-06-2010, 10:27 PM

The below code finds a cell that contains the word "Japan". Then a range is set according to the cell location of the word "Japan". That part is working.

Next, I want to set the variable FoundRow to the Row in which the word Japan was found.
How does the code for that look like?

Please Login or Register  to view this content.

**randell.graybill** · 05-06-2010, 10:34 PM

based on what little information provided I'm not sure if the below will work but you can give it a try. If this doesn't work then I would post a sample set of data along with the rest of your macro. I also included an example on how to put the row numbers in the spreadsheet for storage, in case japan appears more then once.

Please Login or Register  to view this content.

**dschmitt** · 05-06-2010, 11:12 PM

I attached an Excel example.

The example creates a hyperlink for each name on Sheet "SC-Author ranking" to the corresponding author name on Sheet "Author ranking". That is working.

Additionally I want to copy the value in the SC-Sum column of Sheet "SC-Author ranking" to the corresponding author name, column SC-Sum on Sheet "Author ranking". That I don't know how to.

The sheet "Result" illustrates what I want to achieve on sheet "Author ranking".

**randell.graybill** · 05-07-2010, 12:12 AM

Place the following formula in Cell M2 on sheet Author Ranking:

Please Login or Register  to view this content.

or if you wanted it to be done in vba you could do: Add the below code after next JAuth2Rw. However with the formula it would calculate each time formulas are calculated without the need to run the macro.

Please Login or Register  to view this content.

**dschmitt** · 05-07-2010, 12:17 AM

thanks for the tip. Unfortunately I can't do it that way. I need to make it part of the Sub.
I am trying to put the code in the Do - Loop while not loop.

**dschmitt** · 05-07-2010, 12:25 AM

I made an attempt to illustrate more closely what I am trying to do. See attached file.
Now the numbers are copied from sheet "SC-Author ranking" to sheet "Author ranking". But into the wrong cells.

**ShredDude** · 05-07-2010, 12:26 AM

does this work?

Please Login or Register  to view this content.

**dschmitt** · 05-07-2010, 12:32 AM

Yes! That's it. Thanks ShredDude. I attached the solution Excel file.

**randell.graybill** · 05-07-2010, 02:06 AM

I got it to work by doing the following:

Please Login or Register  to view this content.

**dschmitt** · 05-07-2010, 04:19 AM

randell, thanks for you continued interest.

It turns out that the solution that I posted in Example3.xlsm creates errors when I apply it to a large data set.
For that reason I switched to a For - next loop. At least there I know what I am doing. See attached file.
However, considering that I am dealing with large data sets a faster solution would be better.