Author Topic: Find empty cell from Autocad VBA  (Read 4242 times)

0 Members and 1 Guest are viewing this topic.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Find empty cell from Autocad VBA
« on: November 14, 2006, 11:04:51 AM »
I am trying to find the first empty cell in excell spreadsheet, and I cant seem to get past the with statement.  Im not an excell programmer, so Im kinda lost.  Here is the code I grabbed from the internet
Code: [Select]
Sub FindLastCell()
  Dim LastCell As Range
  With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    If IsEmpty(LastCell) Then
      'do nothing
    Else
      Set LastCell = LastCell.Offset(1, 0)
    End If
  End With
  MsgBox LastCell.Row
End Sub

Any help or direction would be greatly appreciated   :mrgreen:
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Find empty cell from Autocad VBA
« Reply #1 on: November 14, 2006, 12:41:08 PM »
this is what  I came up with
Code: [Select]
Public Function GetLastRow() As Long
Dim wks As Excel.worksheet
Const BottomRowNum = 65536
    Set wks = objExcel.ActiveSheet
    Dim i As Long
    For i = 1 To BottomRowNum
        If IsEmpty(wks.Cells(i, 2)) Then
            GetLastRow = i - 1
            Exit Function
        End If
    Next
    GetLastRow = BottomRowNum
End Function
« Last Edit: November 14, 2006, 12:43:03 PM by CmdrDuh »
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Fatty

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #2 on: November 14, 2006, 01:22:52 PM »
Hi, Commandor

Not sure about how it will be work for you
At a first glance seems to be worked as I want

Code: [Select]
Option Explicit
'' ~~~~~~~~~~~~~~~~''
Function FindLastRow()
  Dim LastRow As Range
 
  With ActiveSheet
    Set LastRow = .Cells.SpecialCells(xlCellTypeLastCell)
        LastRow.Activate
  End With
  FindLastRow = LastRow.Row

End Function
'' ~~~~~~~~~~~~~~~~''
Function FindLastColumn() As Long
  Dim LastColumn As Range
 
  With ActiveSheet
    Set LastColumn = .Cells.SpecialCells(xlCellTypeLastCell)
        LastColumn.Activate
  End With
  FindLastColumn = LastColumn.Column
 
End Function
'' ~~~~~~~~~~~~~~~~''
Sub FindLastCell()

  Dim LastCell As Range
  With ActiveSheet
  Set LastCell = .Cells(FindLastRow, FindLastColumn)
  End With
  LastCell.Activate
  MsgBox "Last Cell Address: " & vbCr & LastCell.Address
 
End Sub

Tested on Excel2003 only

Hth

~'J'~

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Find empty cell from Autocad VBA
« Reply #3 on: November 14, 2006, 02:32:44 PM »
OK, any idea on how to format a cell from autocad?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Fatty

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #4 on: November 14, 2006, 03:19:56 PM »
OK, any idea on how to format a cell from autocad?
What do you want exactly,
format text in cell i.e. set bold, italic, color etc, or you need to format
value i.e. general, text, numberformat setting etc?

~'J'~

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Find empty cell from Autocad VBA
« Reply #5 on: November 14, 2006, 03:31:20 PM »
format value like text or number w/ 0 decimal places
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Bryco

  • Water Moccasin
  • Posts: 1849
Re: Find empty cell from Autocad VBA
« Reply #6 on: November 14, 2006, 03:39:14 PM »
CmdrDuh, sometimes it's easier to start a sub in excel just so you can see all the properties available then put it into the autocad vba. I only say this as Im not an excell programmer either.

Fatty

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #7 on: November 14, 2006, 03:40:11 PM »
format value like text or number w/ 0 decimal places
Here I added several properties for imagination
Hope this helps
Code: [Select]
Sub TestFormatACell()

Dim aCell As Range, bCell As Range

Set aCell = ActiveSheet.Range("A1")
Set bCell = Worksheets(3).Cells(1, 2)
aCell.Select
With ActiveCell
.Font.Bold = True
.Font.Italic = True
.Font.Color = RGB(255, 0, 0)
.NumberFormat = "0.0000000"
End With

bCell.Select
With ActiveCell
.VerticalAlignment = xlVAlignCenter
.HorizontalAlignment = xlLeft
.Font.Bold = False
.Font.Italic = False
.Font.Color = RGB(0, 255, 0)
.NumberFormat = "$#,##0.00"
End With

End Sub

~'J'~

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Find empty cell from Autocad VBA
« Reply #8 on: November 14, 2006, 03:41:33 PM »
cool, but how do you format it to be 'text' not a number?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Fatty

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #9 on: November 14, 2006, 04:04:29 PM »
cool, but how do you format it to be 'text' not a number?
You can use these formats:
Code: [Select]
.NumberFormat = "@" '' text formator
Code: [Select]
.NumberFormat = "General" '' general format
~'J'~

Dnereb

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #10 on: November 14, 2006, 04:08:41 PM »
CmdrDuh,

Another way to find out such trivial syntax thingies can be done this way:


1) Open a new workbook Excel
2) Select cell A1
3) Start the macro recorder to record a macro in thisWorkbook
4) in the format menu format -> cell format the cell for text
5) stop the macro recorder
6) Press Alt F11
7) Look at the code in module1

.numberformat =  ........
will be the line you want.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Find empty cell from Autocad VBA
« Reply #11 on: November 14, 2006, 04:12:23 PM »
Cool, I didn't know how to use the recorder

Fatty, thanks, I'll try that
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Fatty

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #12 on: November 14, 2006, 04:13:49 PM »
Cool, I didn't know how to use the recorder

Fatty, thanks, I'll try that
Happy computing, Commandor

Cheers :)

>'J'<

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Find empty cell from Autocad VBA
« Reply #13 on: November 14, 2006, 04:18:44 PM »
It works perfectly.  Next Step, automatic BOM
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Arizona

  • Guest
Re: Find empty cell from Autocad VBA
« Reply #14 on: November 14, 2006, 05:26:49 PM »
Very cool!
Too bad Autodesk hasn't consider real Utility design yet, you are way up on them. :-)