Column number of last column that returns value ignore formulas
 

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

Column number of last column that returns value ignore formulas
 

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)

Column number of last column that returns value ignore formulas
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Column number of last column that returns value ignore formulas
 

On Tuesday, May 27, 2014 12:35:13 PM UTC-7, Claus Busch wrote:
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:


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

Column number of last column that returns value ignore formulas
 

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.

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

Column number of last column that returns value ignore formulas
 

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

Column number of last column that returns value ignore formulas
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Column number of last column that returns value ignore formulas
 

Hi again,

Am Wed, 28 May 2014 07:50:26 +0200 schrieb Claus Busch:

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

change the line above to:
If Len(.Cells(3, i).Value) 0 Then


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Column number of last column that returns value ignore formulas
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Column number of last column that returns value ignore formulas
 

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



or try it with

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





Regards

Claus B.


Thanks, Claus.

Will give it a go.

Howard