Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

I have data that looks like this

Column A - Column U
1/1/2011 "" (ie: blank)
2/1/2011 ""
3/1/2011 ""
4/1/2011 ""
5/1/2011 ""
6/1/2011 50
..... 75
12/1/2011 100

Where the price in column U is only present for the month following the current month, all previous months are blank. As time rolls on, there will be more blanks throughout the year and when the next year comes, the first month rolls to the next year.

I am backing into some extremely complex code that is written, so I'm just trying to change a few lines but am having difficulty figuring out how to reference the first row with a value in Column U. Currently, the spreadsheet removes the first line as each new month changes, making the price i'm seeking ALWAYS in cell U2 (first month in the list). I need that to be more dynamic.

What is the VBA equivalent for finding this row #? The code already is looking for the second row, so I need to change that to be variable. I want to say a vlookup is in order, but have never coded it.

Thanks for any help! So glad I found this thread, expect me to be a regular now...

# ¿ May 24, 2011 19:15

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 16:51

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

gwar3k1 posted:

Or not even a loop:

iRow = Range("U1:U60000").End(xlDown).Row

There are formulas in there to produce the "", so it will go to the very end.

The loop should work just well! I have to put another if statement in there to reference a different column for two different names that come up, but it'll work great.

Thanks!

# ¿ May 24, 2011 19:39

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

esquilax posted:

If you're using VBA, the easiest way would be to just use a Do/While loop on the cells in Column U (Column 21) until you find a cell that isn't blank.

e.g.
code:
FirstValueRow = 2  'so it skips the heading
Do While Cells(FirstValueRow, 21).Value = ""
FirstValueRow = FirstValueRow + 1
Loop

To follow up on this, after it finds the first cell with data in it, how can I assign a variable to that cell location? I just found another line of code that needs to know exactly where that cell is, versus what row.

# ¿ May 26, 2011 15:49

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

esquilax posted:

In the "Cells()" function, the syntax is cells(row, column). In my example, the column was 21 (U is the 21st letter).

You will need to create a variable (call it "FirstValueColumn" or something, it doesn't really matter), and make it equal to whatever column you're trying to look in. If you're only looking in column U, FirstValueColumn=21.

You can then use Cells(FirstValueRow, FirstValueColumn) to point to the exact cell you want.

Does that help?

So then Variable1 = cells(FirstvalueRow, FirstValueColumn) would make a variable with that location?

# ¿ May 26, 2011 18:00

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

esquilax posted:

I don't think so, but you can basically use 'cells(FirstvalueRow, FirstValueColumn)' for most purposes that you would want to use a variable for.

What do you want to use it for?

Currently the code is:

nextMonthForwardPrice = Range("U3")

It needs to be

nextMonthForwardPrice = Range("U-whateverthefirstnonblankcellis")*

* with the blanks being formulas inputting "" as the value

# ¿ May 26, 2011 19:45

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 16:51

TraderStav: May 19, 2006; It feels like I was standing my entire life and I just sat down

esquilax posted:

Is nextMonthForwardPrice supposed to be a range (e.g. U1:U10), or just the value of whatever that cell is (e.g. U10)? If it's the latter, you can just use:

nextMonthForwardPrice = Cells(FirstvalueRow, FirstValueColumn).Value

If it's the former and you want it to be a range of values beginning at U1 and ending at that cell, you can use:
Dim nextMonthForwardPrice as Range
Set nextMonthForwardPrice = Range("U1", Cells(FirstvalueRow, FirstValueColumn))

Usually when you see code like "Range("$A$1")" it means someone used the "Record Macro" deal, which returns very ugly code.

It's not a range, so the .value will be the ticket.

Thanks!

# ¿ May 26, 2011 21:16

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread