Re: Get next empty Column?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Norman Jones (normanjones_at_whereforartthou.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 23:12:43 +0100

Hi James,

I omitted to qualify the code to refer to Sheet2:

    Dim Rng As Range
    Const rw As Long = 1 '<<====== CHANGE

    With Sheets("Sheet2") '<<====== CHANGE
        If IsEmpty(.Cells(rw, 1)) Then
            Set Rng = .Cells(rw, 1)
        Else
            Set Rng = .Cells(rw, Columns.Count). _
                      End(xlToLeft)(1, 2)
        End If
    End With
    MsgBox Rng.Address 

---
Regards,
Norman
"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:OLu56AQnEHA.4004@TK2MSFTNGP10.phx.gbl...
> Hi James,
>
> To set a variable Rng to the first empty cell in row 1 on sheet2, try:
>
> Dim Rng As Range
> Const rw As Long = 1 '<<====== CHANGE
> If IsEmpty(Cells(rw, 1)) Then
>    Set Rng = Cells(rw, 1)
> Else
>    Set Rng = Cells(rw, Columns.Count). _
>                                End(xlToLeft)(1, 2)
> End If
> MsgBox Rng.Address
>
>
> Change the rw value to reflect the row that you use to test for an empty 
> column.
>
>
> ---
> Regards,
> Norman
>
>
>
> "James" <j@j.net> wrote in message 
> news:eXZA1sPnEHA.3968@TK2MSFTNGP11.phx.gbl...
>>I want the button to copy from one sheet and each time I'd like it to 
>>place
>> the numbers in the next available column
>>
>> Do I need to loop and check for emptiness?
>>
>> Thanks
>>
>>
>
>

Relevant Pages

  • Re: First MsgBox. Need help
    ... to find the row on Sheet2 and use that to check cell values... ... MsgBox "Date not there, ... > data entry process. ...
    (microsoft.public.excel.programming)
  • Re: CopyHere
    ... msgbox Err.Description ... "James" wrote in message ... Const FOF_NOCONFIRMMKDIR = &H200& 'Do not confirm the creation of a new directory if the operation requires one to be created ... Am I missing something or is MS missing something? ...
    (microsoft.public.scripting.vbscript)
  • Re: Get next empty Column?
    ... >> Hi James, ... >> Dim Rng As Range ... >> MsgBox Rng.Address ... >> Regards, ...
    (microsoft.public.excel.programming)
  • Re: properties of object on another sheet
    ... Private Sub CommandButton1_Click ... MsgBox Sheets.TextBox1.Text ... Sheets("Sheet2") is the one named Sheet2, ... > Dim strServerName, strDatabaseName As String ...
    (microsoft.public.excel.programming)
  • Re: MsgBox can not display content completely in Access 2003
    ... Ken Snell <MS ACCESS MVP> "James via AccessMonster.com" wrote in message ... > I developed a database in Access 97. ... MsgBox works fine in Access 97, but> will only display part of strings in Access 2003 instead of> full of them. ...
    (microsoft.public.access.formscoding)