Column number of last column that returns value ignore formulas

[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

 

[L. Howard]

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)

 

[Claus Busch]

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

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.

 

[L. Howard]

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:

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

 

[isabelle]

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

other activity in the workbook.

 

[L. Howard]

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

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

 

[Claus Busch]

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.

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.

 

[Claus Busch]

Hi Howard,

Am Wed, 28 May 2014 07:59:57 +0200 schrieb Claus Busch:

If Len(.Cells(3, i).Value) > 0 Then

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


Regards
Claus B.

 

[L. Howard]

or try it with

Lcol = 23 - WorksheetFunction.CountBlank(Range("A3:W3"))





Regards

Claus B.

Thanks, Claus.

Will give it a go.

Howard