Convert code rows to columns

Hi,
This code was posted by Norman Jones i believe. It works great.

Please Login or Register  to view this content.

I tried to change it for Columns instead of Rows. what am i doing wrong?

Please Login or Register  to view this content.

Dave

Function LastCol(SH As Worksheet)
On Error Resume Next
LastColumn = SH.Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
On Error GoTo 0
End Function

try this

Sub test()
Cells(1, Lastcol(ActiveSheet) + 1).Value = "Hi this is one column right
the Last Used column"
End Sub

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

--


Gary


"Piranha" <Piranha.1wtlme_1129172705.3087@excelforum-nospam.com> wrote in
message news:Piranha.1wtlme_1129172705.3087@excelforum-nospam.com...
>
> Hi,
> This code was posted by Norman Jones i believe. It works great.
>
> Code:
> --------------------
> Sub test()
> Cells(LastRow(ActiveSheet) + 1, "A").Value = "Hi this is one row below
> the Last Used Row"
> End Sub
>
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
> --------------------
>
> I tried to change it for Columns instead of Rows. what am i doing
> wrong?
>
> Code:
> --------------------
> Sub test()
> Cells(LastCol(ActiveSheet) + 1, "A").Value = "Hi this is one column past
> the Last Used Column"
> End Sub
>
> Function LastCol(SH As Worksheet)
> On Error Resume Next
> LastColumn = SH.Cells.Find(What:="*", _
> After:=SH.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
> --------------------
>
> Dave
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=475689
>

Gary & mudraker,

Thanks to both of you. I see the errors of my ways.

Dave

Originally Posted by Gary Keramidas

try this

Sub test()
Cells(1, Lastcol(ActiveSheet) + 1).Value = "Hi this is one column right
the Last Used column"
End Sub

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

--


Gary


"Piranha" <Piranha.1wtlme_1129172705.3087@excelforum-nospam.com> wrote in
message news:Piranha.1wtlme_1129172705.3087@excelforum-nospam.com...
>
> Hi,
> This code was posted by Norman Jones i believe. It works great.
>
> Code:
> --------------------
> Sub test()
> Cells(LastRow(ActiveSheet) + 1, "A").Value = "Hi this is one row below
> the Last Used Row"
> End Sub
>
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
> --------------------
>
> I tried to change it for Columns instead of Rows. what am i doing
> wrong?
>
> Code:
> --------------------
> Sub test()
> Cells(LastCol(ActiveSheet) + 1, "A").Value = "Hi this is one column past
> the Last Used Column"
> End Sub
>
> Function LastCol(SH As Worksheet)
> On Error Resume Next
> LastColumn = SH.Cells.Find(What:="*", _
> After:=SH.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
> --------------------
>
> Dave
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=475689
>

I suggest you made typo error only.

1 You get the LastColumn correctly;
2 The function returns LastCol which is NOT set;
3 Cells is defined by row then column. You did not switch them over.

Regards

"Piranha" wrote:

>
> Hi,
> This code was posted by Norman Jones i believe. It works great.
>
> Code:
> --------------------
> Sub test()
> Cells(LastRow(ActiveSheet) + 1, "A").Value = "Hi this is one row below the Last Used Row"
> End Sub
>
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
> --------------------
>
> I tried to change it for Columns instead of Rows. what am i doing
> wrong?
>
> Code:
> --------------------
> Sub test()
> Cells(LastCol(ActiveSheet) + 1, "A").Value = "Hi this is one column past the Last Used Column"
> End Sub
>
> Function LastCol(SH As Worksheet)
> On Error Resume Next
> LastColumn = SH.Cells.Find(What:="*", _
> After:=SH.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
> --------------------
>
> Dave
>
>
> --
> Piranha
> ------------------------------------------------------------------------
> Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
> View this thread: http://www.excelforum.com/showthread...hreadid=475689
>
>