Userform stumped

[FoRmEd]

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.

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]

Re: Next Clear Cell using .Select

Essentially, the user is interfaced with a Userform. The userform interacts with Sheet "PHZ1" Each time the user hits submit, the code needs to find the next empty cell in a column, input the data, then clear the userform for the next entry. So your correct when you say it's used to add a new record.

The twist is that it doesn't just go down the same column. Instead it has to move to another column and continue there.

The CIScol issue was the wrong approach and it is confusing to me as well.
But here is what was meant.
CIScol = B4:B43
CIScol2 = I4:I43
CIScol3 = B47:B86
CIScol4 = I47:I86

So the loop essentially starts at B4 and ends at I86, Oh and there is a loop for each of the other columns as well(CLSD,Addt,Route,FLIP). The picture below shows what I mean.

 

[Zack Barresse]

Re: Next Clear Cell using .Select

Same topic. Threads merged. Please stick to one thread per topic.

 

[Norie]

Re: Next Clear Cell using .Select

So you actually know the ranges involved, and know what the next range should be when one range has been filled up?

Well that shouldn't be too hard to figure out, but things would be a lot easier if you didn't have these non-contiguous ranges.

Is there some reason you can't use the same 6 columns (B to F) for all the records?

 

[FoRmEd]

Ya, The requester wants it like that for printing, so page 1 has entries 1-80 and page 2 has entries 81-160.

 

[Norie]

If this format is for printing, why does the data need to be stored in this format?

You could store the data in a much more usable format, and if you did it wouldn't be too hard to print it out as required.

 

[FoRmEd]

If I had a sample showing what you are talking about, I would be able to show the requester. Thank you for your help Norie, your patience means alot. I like how you ask me questions which enables me to go looking for the answer rather than just giving me the answer. Plus I think I can be hard to understand because my mind goes six different directions and its hard to tell which way Im going sometimes.

 

[Norie]

A sample of what exactly?

I can mock up a userform and some other stuff if that's what you mean.

Though if you could post some sample data (doesn't need to be 'real') it would makes things a bit easier.

 

[FoRmEd]

Sure, Im at work, so I will post the Workbook after I get home late tonight.

 

[FoRmEd]

The sample file has passwords on the worksheets but not the editor. The password is simply "****" and you can find it in the editor. The code that is in there is incorrect but atleast you can see the format of what they want.

Ok here is the link for downloading the sample workbook:
https://files.me.com/donaber/1b0ezr

 

[Norie]

The password is incorrect.

The file does show how it's wanted, but what I was suggesting was not to have it like that.

Not for storing the actual data anyway.

There's also no data on the sheet and there are formulas in protected cells.

Well I think there are formulas, I can't actually access them.