insert cellls vba

[rakesh seebaruth]

Hi Guys

I have the following code

Cells(LR, 28).Value = txtCNum4.Value

In my useform txtCNum4 is a text box name Lease Period

What i want is when the user enters a number let's say 10.

vba inserts 10 cells after Cells(LR, 28) and i also want vba to add a heading month1 to month10 to those cells

thanks/regards

rakesh

 

[Paul Ked]

What's the rest of the code in that sub?

 

[rakesh seebaruth]

Private Sub cmdAdd_Click()


Dim LR As Long
'Make Sheet1 active
Sheet1.Activate


'Determine emptyRow
LR = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information






Cells(LR, 1).Value = txtName1.Value
Cells(LR, 2).Value = txtDob.Value
Cells(LR, 3).Value = txtPob.Value
Cells(LR, 4).Value = txtDistrict.Value
Cells(LR, 5).Value = txthisher.Value
Cells(LR, 6).Value = txtBc.Value
Cells(LR, 7).Value = txtCso.Value
Cells(LR, 8).Value = txtRegd.Value
Cells(LR, 9).Value = txtNic.Value
Cells(LR, 10).Value = txtJob.Value
Cells(LR, 11).Value = txtAdd.Value
Cells(LR, 12).Value = txtMs.Value
Cells(LR, 13).Value = ComboBox1.Value
Cells(LR, 14).Value = ComboBox2.Value
Cells(LR, 15).Value = txtCn.Value
Cells(LR, 16).Value = txtName1.Value
Cells(LR, 17).Value = txtCNum1.Value
Cells(LR, 18).Value = txtCNum1.Value
Cells(LR, 19).Value = txtName1.Value
Cells(LR, 20).Value = txtDes.Value
Cells(LR, 21).Value = txtSt.Value
Cells(LR, 22).Value = txtEn.Value
Cells(LR, 23).Value = txtCo.Value
Cells(LR, 24).Value = txtNav.Value
Cells(LR, 25).Value = txtLocation.Value
Cells(LR, 26).Value = txtHi.Value
Cells(LR, 27).Value = txtDd.Value
Cells(LR, 28).Value = txtCNum4.Value
Cells(LR, 29).Value = txtCNum5.Value
Cells(LR, 30).Value = txtCNum6.Value
Cells(LR, 31).Value = txtCNum8.Value
Cells(LR, 32).Value = txtCNum7.Value
Cells(LR, 33).Value = txtCNum9.Value
end sub

 

[Paul Ked]

190 posts and still not using Code tags?

 

[rakesh seebaruth]

sorry

 

[Paul Ked]

10 cells after Cells(LR, 28).Value would overlap what you've got. Do you mean 10 rows?

Or do you mean 1 header row with 10 columns and then the cells below with the data?

 

[rakesh seebaruth]

i'm sorry its after Cells(LR, 33).Value = txtCNum9.Value. Insert 10 columns after that.

thanks

 

[Paul Ked]

... Previous code
Cells(LR, 32).Value = txtCNum7.Value
Cells(LR, 33).Value = txtCNum9.Value
    Dim i&
    For i = 1 To txtCNum4.Value
        Cells(LR, 33 + i) = "Month " & i
    Next
End Sub

 

[rakesh seebaruth]

The headings month1 to month10 are on range(AI3:AR3).It should be on range(AI1:AR1)

thanks

 

[Paul Ked]

They are on row LR, like you asked:

i'm sorry its after Cells(LR, 33).Value = txtCNum9.Value. Insert 10 columns after that.