Filling datas to Named Range – Help

bong25 · May 16, 2011

Hi again,

I am working with new project in excel vba userform.

In excel I have named range of cells “A11:Z21” to “IOW”. Currently the named range “IOW” does not have a single data. I have no idea how to get the first blank cell (say B11) inside IOW and shift the data from userform controls to that, then the next data will shift to the bottom of the cell (for better word “lastrow”).

How can I achieve this in vba?

Thanks again in advance.

bong25 · May 16, 2011

Currently i have this code:

Code:

Private Sub CommandButton1_Click()
Dim ctlCB As Control
Dim myR As Range

Sheets("Sheet1").Select
Set myR = Range("IOW")
myR.Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select

For Each ctlCB In Me.Controls
    If TypeName(ctlCB) = "CheckBox" Then
        If ctlCB.Value = True Then
            MsgBox "The Value of: ctlCB = " & ctlCB.Caption & " " & ctlCB & " and cB1 : " & cB1
            ActiveCell.Value = ctlCB.Caption
            ActiveCell.Offset(1, 0).Activate
        End If
    End If
Next ctlCB

End Sub

But the problem is once new data to shift, it goes on the next empty column instead of going to the next empty row.

Can anybody got an idea?

Sektor · May 16, 2011

Code:

Sub ReadNamedRangeCells()

    Dim i As Long
    
    For i = 1 To Range("IOW").Count
        ' Process.
    Next
    
    ' Get cell B11.
    MsgBox Range("IOW").Cells(2)
    ' Or:
    MsgBox Range("IOW")(2)

End Sub

bong25 · May 16, 2011

Thanks for quick reply I will try that.

bong25 · May 16, 2011

Hmm, That kinda confuses me more, can we not just correct what I have now?, that if its possible.

bong25 · May 16, 2011

Any Idea, please

bong25 · May 16, 2011

The Named Range "IOW" in Sheet1 always empty once the transaction is finished. This is the reason why I have to find the first empty cell.

The reason for naming the range is because the above rows are changing in a way of rows deleted or added depending on the situation.

Anybody? Plz

Filling datas to Named Range – Help

bong25

New Member

Excel Facts

bong25

New Member

Sektor

Well-known Member

bong25

New Member

bong25

New Member

bong25

New Member

bong25

New Member

Similar threads

Forum statistics

Share this page

Filling datas to Named Range – Help

bong25

New Member

Excel Facts

bong25

New Member

Sektor

Well-known Member

bong25

New Member

bong25

New Member

bong25

New Member

bong25

New Member

Similar threads

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