Column number of last column that returns value ignore formulas

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

With this test snippet:

Col A to C = text

Col D to W = formulas like: =IF(Mon!D3="","",Mon!D3)

LastCol to return column number of the cell in row 3 that the formula has returned a VALUE.

Thanks.
Howard

Sub LastColumnInOneRow()
'Find the last used column in a Row: row 3 in this example (23)
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol
End Sub
 
This line really is not pertinent to my cause. Came with the example and the 23 is col W as a note to me. Sorry.

'Find the last used column in a Row: row 3 in this example (23)
 
Hi Howard,

Am Tue, 27 May 2014 12:07:06 -0700 (PDT) schrieb L. Howard:
Sub LastColumnInOneRow()
'Find the last used column in a Row: row 3 in this example (23)
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol
End Sub
Click to expand...

I don't know if I understand your problem.
Your macro finds the last used column in row 3.
If you want to find the last error in row 3 then try:

Sub LastColumnInOneRow()
Dim i As Long

With ActiveSheet
For i = 23 To 1 Step -1
If IsError(.Cells(3, i)) Then
MsgBox i
Exit For
End If
Next
End With

End Sub


Regards
Claus B.
 
Hi Howard,



Am Tue, 27 May 2014 12:07:06 -0700 (PDT) schrieb L. Howard:













I don't know if I understand your problem.

Your macro finds the last used column in row 3.

If you want to find the last error in row 3 then try:
Click to expand...
Hi Claus,

That last used column in row 3 is always column W as that is as far to the right the formulas go. D to W are all formulas, some will have values returned some will not. This is a changing thing with other activity in the workbook.

If column H is the last cell to returns a value, that is the column number I want. Cells past H on over to W with no values showing are ignored.

I will then use that column number in a resize code to color fill the row from column A to that returned value in H. Next time it may column R is the last occurring value.

I'm looking for help because I can't make the code ignore a cell with a formula but showing NO value but recognize a cell with a value showing.

I'll test your code to make sure it does or does not do what I am looking for.

Thanks.
Howard
 
hi,

lastcell= Evaluate("MIN(IF(A3:W3="""",COLUMN(A3:W3)))-1")

also with a variable

i = 3
x = Evaluate("MIN(IF(A" & i & ":W" & i & "="""",COLUMN(A" & i & ":W" & i & ")))-1")

isabelle

Le 2014-05-27 17:15, L. Howard a écrit :
That last used column in row 3 is always column W as that is as far to the right the formulas go. D to W are all formulas,
some will have values returned some will not. This is a changing thing with
Click to expand...
other activity in the workbook.
 
lastcell= Evaluate("MIN(IF(A3:W3="""",COLUMN(A3:W3)))-1")



also with a variable



i = 3

x = Evaluate("MIN(IF(A" & i & ":W" & i & "="""",COLUMN(A" & i & ":W" & i & ")))-1")



isabelle
Click to expand...

Hi isabelle,

You code lines work as you would expect, thanks.

My problem is folding them into my code to my satisfaction. The values that will show in the cells are the time of day for sign IN and sign OUT.

Formulas go from column D to column W.

So a row will be a sign in time, then a sign out time and so on over to column W.

When a sign in time is entered (on another sheet) the formula will return that time to this row and if it is an IN time then the row from column A to that IN time is filled with green. If the time returned by the formula is a OUT time then the row from Column A to that OUT time is filled with red.

I am having a tiny bit of success with the green but does not display consistently for some reason. And with identical code for the red fill I get nothing.

Thanks for the code lines, at least I can count on them if I give them proper direction with my code.

Howard
 
Hi Howard,

Am Tue, 27 May 2014 14:15:04 -0700 (PDT) schrieb L. Howard:
I'm looking for help because I can't make the code ignore a cell with a formula but showing NO value but recognize a cell with a value showing.
Click to expand...

try:

With ActiveSheet
For i = 23 To 1 Step -1
If Len(.Cells(3, i).Value) Then
MsgBox i
Exit For
End If
Next
End With


Regards
Claus B.
 
Hi Howard,

Am Wed, 28 May 2014 07:59:57 +0200 schrieb Claus Busch:
If Len(.Cells(3, i).Value) > 0 Then
Click to expand...

or try it with
Lcol = 23 - WorksheetFunction.CountBlank(Range("A3:W3"))


Regards
Claus B.
 
Back
Top