Userform stumped

FoRmEd · Mar 4, 2011

Next Clear Cell using .Select

Am I correct when assuming .Select can be used when using a range of cells, then when that range of cells is full, move to the next defined Range of cells.

So maybe something like this.

Rich (BB code):

Sub cmdAdd_Click()
   ActiveSheet.Unprotect Password:="*"
 
If Me.textCIS = "" Then
    MsgBox ("Enter a valid CIS Number")
Else
 
' identify workspace locations 
' this part im unsure about and it wont debug
' these are the seperate named ranges but I dont
' now where to add them
       HeaderRowP1 = PHZ1.Range("HeaderRowP1").Row
       CIScol = PHZ1.Range("CIScol,CIScol2,CIScol3,CIScol4").Cells
       CLSDcol = PHZ1.Range("CLSDcol,CLSDcol2,CLSDcol3,CLSDcol4").Cells
       Addtcol = PHZ1.Range("Addtcol,Addtcol2,Addtcol3,Addtcol4").Cells
       Routecol = PHZ1.Range("Routecol,Routecol2,Routecol3,Routecol4").Cells
       FLIPcol = PHZ1.Range("FLIPcol,FLIPcol2,FLIPcol3,FLIPcol4").Cells
 
    RowNum = HeaderRowP1 + 1
 
    ' I got it passed that debug but it stops here
    Do While PHZ1.Cells(RowNum, CIScol) <> ""
       RowNum = RowNum + 1
    Loop
 
End If
   ActiveSheet.Protect Password:="*"
End Sub

FoRmEd · Mar 5, 2011

I am at a lose, I dont know why it wont work. Here is my code, and if you need more info please let me know.

Rich (BB code):

Private Sub cmdAdd_Click()
 
   If Me.textCIS = "" Then
        MsgBox ("Enter a valid CIS Number")
    Else
 
    'identify workspace locations
        HeaderRowP1 = PHZ1.Range("HeaderRowP1").Row
        CIScol = PHZ1.Range("CIScol,CIScol2,CIScol3,CIScol4").Cells
        CLSDcol = PHZ1.Range("CLSDcol,CLSDcol2,CLSDcol3,CLSDcol4").Cells
        Addtcol = PHZ1.Range("Addtcol,Addtcol2,Addtcol3,Addtcol4").Cells
        Routecol = PHZ1.Range("Routecol,Routecol2,Routecol3,Routecol4").Cells
        FLIPcol = PHZ1.Range("FLIPcol,FLIPcol2,FLIPcol3,FLIPcol4").Cells
 
    'find first clear row
    
        RowNum = HeaderRowP1 + 1
 
'this is where it debugs
        Do While PHZ1.Cells(RowNum, CIScol) <> ""
        RowNum = RowNum + 1
        Loop
 
    'copy the data to the database
    PHZ1.Cells(RowNum, CIScol) = Me.textCIS
 
 
        If Me.optCLSD Then
            PHZ1.Cells(RowNum, CLSDcol) = "1"
        Else
            PHZ1.Cells(RowNum, CLSDcol) = ""
        End If
        If Me.optAddt Then
            PHZ1.Cells(RowNum, Addtcol) = "1"
        Else
            PHZ1.Cells(RowNum, Addtcol) = ""
        End If
        If Me.optRoute Then
            PHZ1.Cells(RowNum, Routecol) = "1"
        Else
            PHZ1.Cells(RowNum, Routecol) = ""
        End If
        If Me.optFLIP Then
            PHZ1.Cells(RowNum, FLIPcol) = "1"
        Else
            PHZ1.Cells(RowNum, FLIPcol) = ""
        End If
 
End If
    'clear the data
    Me.textCIS.Value = ""
    Me.optCLSD.Value = "0"
    Me.optAddt.Value = "0"
    Me.optRoute.Value = "0"
    Me.optFLIP.Value = "0"
 
    Me.textCIS.SetFocus
 
End Sub

AlphaFrog · Mar 5, 2011

What are these variables CIScol, CIScol2, CIScol3, CIScol4 ?
Named ranges? Range variables? Column numbers? Exactly what? Maybe give an example.

Describe in words what you want to loop through.

With this line if code...

Code:

Do While PHZ1.Cells(RowNum, CIScol) <> ""

...VBA is expecting CIScol to be an integer (column number) or a string that is a column letter. If it's not one of those two, you'll get an error.

FoRmEd · Mar 5, 2011

Normally I find the next clear cell and keep running straight down a column but now I need the code to jump to a different area. So:

B4:B43 then to I4:I43 and then to B47:B86 and finally I47:I86

The CIScol is one of those ranges B4:B43 and each set added a number.
So CIScol2 = I4:I43

There is a total of 5 columns for it.

AlphaFrog · Mar 5, 2011

I'm still not sure what exactly CIScol2 is. I know you say it's a range like I4:I43 but is it a variable type Range or String or exactly what? It matters the Type of variable it is.

Here is an example where you can loop through each cell in a multi-area range.

Code:

Sub Example()

    Dim CIScol As Range, cell As Range
    Dim PHZ1 As Worksheet
    
    Set PHZ1 = ActiveSheet
    
    Set CIScol = PHZ1.Range("B4:B43, I4:I43, B47:B86, I47:I86")
    
    For Each cell In CIScol
        MsgBox cell.Address
    Next cell
    
End Sub

FoRmEd · Mar 5, 2011

I understand what you are speaking of now. It wouldn't be a variable then. Would it be a string like you were just talking about?

If b4:b43 is full Then
Move to next i4:i43
If i4:i43 is full Then
Move to next etc..

Obviously that isn't real code but it helps me see the bigger picture.

Your code makes sense, I need to read more of my VBA bible.I will go test it here shortly.

Zack Barresse · Mar 5, 2011

Re: Next Clear Cell using .Select

Hi,

Can you post all of your code? You haven't declared any variables, and I'm not sure what PHZ1 is (assuming it's the parent worksheet declared somewhere else). The way you are referencing the ".Cells" method doesn't look right. I'm not sure what you're trying to do, so I can't really give you a decent answer one way or another. Perhaps you can, in addition to posting all of your code, give some background information on the scope of your project?

FoRmEd · Mar 5, 2011

Ok so before I put it in I will post it here to see if I completely understand
what the code is doing. Just as some additional info; the image below
shows the 5 columns that I am using.

Rich (BB code):

Sub Example()
    'Once CIScol is set as Range I can't set anything else
    'as a Range right?  If so, I have 4 more columns to add.
    Dim CIScol As Range, cell As Range
    Dim PHZ1 As Worksheet
    
        Set PHZ1 = ActiveSheet
    
      Set CIScol = PHZ1.Range("B4:B43, I4:I43, B47:B86, I47:I86")
      Set CLSDcol =  PHZ1.Range("C4:C43, J4:J43,  C47:C86, J47:J86")
      Set Addtcol = PHZ1.Range("D4:D43, K4:K43,  D47:D86, K47:K86")
      Set Routecol =  PHZ1.Range("E4:E43, L4:L43,  E47:E86, L47:L86")
      Set FLIPcol =  PHZ1.Range("F4:F43, M4:M43,  F47:F86, M47:M86")
   'added CLSDcol,Addtcol,Routecol,FLIP but I need to research what the 
   '   MsgBox cell.Address is supposed to do.

For Each cell In CIScol
        MsgBox cell.Address
    Next cell
    
End Sub

FoRmEd · Mar 6, 2011

Re: Next Clear Cell using .Select

Here is the thread with more detail about the code. It is used with a Userform, basicly the Value in the textbox gets submitted into the Range. The catch, there is multiple Ranges rather than one column.

http://www.mrexcel.com/forum/showthread.php?t=533754

Norie · Mar 6, 2011

Re: Next Clear Cell using .Select

I'm not sure if you should have perhaps continued in the original thread, but even after reading that it isn't clear what you actually want to do.

As AlphaFrog asked in the other thread - what in words is this code meant to do?

Are you trying to put a single value into multiple cells and those cells are in the named ranges you appear to be referring to?

Or are you dealing with multiple records and you want to find the next empty row to add a new record?

This just doesn't quite make sense.

Code:

CIScol = PHZ1.Range("CIScol,CIScol2,CIScol3,CIScol4").Cells

If PHZ1 is a worksheet and CIScol etc are name ranges you'll end up with an array of values in the variable CISCol.

Those values will be from the first named range.

Userform stumped

FoRmEd

Board Regular

Excel Facts

FoRmEd

Board Regular

AlphaFrog

MrExcel MVP

FoRmEd

Board Regular

AlphaFrog

MrExcel MVP

FoRmEd

Board Regular

Zack Barresse

MrExcel MVP

FoRmEd

Board Regular

FoRmEd

Board Regular

Norie

Well-known Member

Forum statistics

Share this page

Userform stumped

Board Regular

Excel Facts

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock