id not auto increment and data overwritten using loop

[aysel1989]

Hi everyone, I"m still new in VBA and currently working on to develop data entry form in excel. I'm facing errors where my ID cannot auto increment while inserting the data=Country with 10 rows once the button was clicked. My codes are getting errors by overwritten the previous data for next clicked button. ERRORS in LOOPING and ID AUTO INCREMENT !!

Sub test()

Sheet1.Activate
Range("A1").End(xlDown).Offset(1,0).Select


ActiveCell.Value = ActiveCell.Value + 1
ActiveCell.Offset(0, 1).Value = cboCountry.Value


Dim loopCounter As Integer


For loopCounter = 1 To 10
ActiveCell.Value = ActiveCell.Value = loopCounter + 1
ActiveCell.Offset(loopCounter, 1).Value = cboCountry.Value


Next loopCounter

End sub

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]USA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub test()

    With Sheet1.Range("A1").End(xlDown).Offset(1, 0)
        .Value = .Offset(-1).Value + 1
        .AutoFill .Resize(10), xlFillSeries
        .Offset(, 1).Resize(10).Value = cboCountry.Value
    End With

End Sub

 

[Kuljack]

I do not quite understand what your goal is here. Are you looking to bring in country data into column B from another source, or is this predetermined. Where is cboCountry.value being obtained from?

Maybe detail out what you expect your result to be and we can go from there.

Also, I think your loop counter is not written the provide the result you want if you have headers... maybe this would work better there.

Dim loopCounter As Integer

For loopCounter = 2 To 10

ActiveCell.Offset(loopCounter, 1).Value = cboCountry.Value
ActiveCell.Value = ActiveCell.Value = loopCounter + 1

Next loopCounter

 

[Snakehips]

aysel1989,,

Maybe not the complete answer but at first sight...

Sub test()

Sheet1.Activate
Range("A1").End(xlDown).Offset(1, 0).Select




ActiveCell.Value = ActiveCell[COLOR=#ff0000].Offset(-1, 0).Value[/COLOR] + 1  [COLOR=#008000]' use 1 row negative offset to get previous Id number (Active cell initially is 0)[/COLOR]
ActiveCell.Offset(0, 1).Value = cboCountry.Value




Dim loopCounter As Integer


[COLOR=#008000]' not sure what you are wanting here*[/COLOR]
For loopCounter = 1 To 10
ActiveCell.Value = ActiveCell.Value [COLOR=#ff0000]=[/COLOR] loopCounter + 1.  '  the second [COLOR=#ff0000]= [/COLOR][COLOR=#008000]makes that a test of is active cell = to loopcounter +1 and returns TRUE or False
'do you want...   ActiveCell.Value = ActiveCell.Value + loopCounter + 1.  ??????????[/COLOR]

ActiveCell.Offset(loopCounter, 1).Value = cboCountry.Value




Next loopCounter


End Sub

 

[aysel1989]

Hi everyone

! Thanks for helping me out. I made some changes to the codes yet the number only populated for first 10 rows of data when i clicked the macro button.

Is there any ways that when i clicked 2nd times on macro button, the number 11-20 can auto increment downwards? Something wrong with my codes
Here's my codes:

sub test()
Dim iRow As Integer  'variable for position controlDim nextRow As Integer


iRow = Sheets("Engine").Range("A4").Value + 1 'make variable equal to counta formula on worksheet


''begin input data into database''
Sheets("pnl_1").Range("Data_Start").Offset(iRow, 0).Value = iRow 'number
Sheets("pnl_1").Range("Data_Start").Offset(iRow, 1).Value = Combo_Country 'country


''end input data into database''


nextRow = Sheets("Engine").Range("A4").Value


For x = 1 To 9
For nextRow = 5 To 13
Cells(x + 5, 1).Value = x + 1
Cells(iRow + nextRow, 2).Value = Combo_Country


Next nextRow
Next x

End Sub

[URL]https://imgur.com/B690Xzk[/URL]

 

[Peter h]

Hi everyone

! Thanks for helping me out. I made some changes to the codes yet the number only populated for first 10 rows of data when i clicked the macro button.

Is there any ways that when i clicked 2nd times on macro button, the number 11-20 can auto increment downwards? Something wrong with my codes
Here's my codes:

sub test()
Dim iRow As Integer  'variable for position controlDim nextRow As Integer


iRow = Sheets("Engine").Range("A4").Value + 1 'make variable equal to counta formula on worksheet


''begin input data into database''
Sheets("pnl_1").Range("Data_Start").Offset(iRow, 0).Value = iRow 'number
Sheets("pnl_1").Range("Data_Start").Offset(iRow, 1).Value = Combo_Country 'country


''end input data into database''


nextRow = Sheets("Engine").Range("A4").Value


For x = 1 To 9
For nextRow = 5 To 13
[COLOR=#ff0000]Cells(x + 5, 1).Value = x + 1
Cells(iRow + nextRow, 2).Value = Combo_Country[/COLOR]


Next nextRow
Next x

End Sub

https://imgur.com/B690Xzk

It's overwriting your data because your ranges never change after running the macro. Where you reference your ranges (The red highlighted text) you use "x" which will always start as 1, and "iRow" which you have set to whatever value is in "A4" + 1. So where you put "Cells(x + 5, 1)", your loop is set to x = 1 To 9, so when it loops the first time you are telling it to put the value "2" (x + 1) into Cell "A6" (Cells(1 + 5, 1)). Does that make sense? It will continue the loop 9 times since you have x set to "1 To 9", but when you run the macro again it will still start at "A6" again because you've hardcoded the cell reference to be "Cells(x + 5, 1)".

I'm not exactly sure where your data starts or what you're referencing "A4" for, so if we need to make any adjustments or changes, we should be able to pretty easily. Try this code, and see if it's what you're wanting.

Dim ws As Worksheet
Dim lRow As Long
Dim x As Integer
Set ws = ThisWorkbook.Sheets("Engine")
lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To 10
If lRow < 5 Then lRow = 5  [COLOR=#008000] 'This ensures data starts in Row 6[/COLOR]
    ws.Cells(lRow + 1, 1) = ws.Cells(lRow, 1).Value + 1    [COLOR=#008000]'This increments the data from the last cell with data[/COLOR]
    ws.Cells(lRow + 1, 2) = Combo_Country  [COLOR=#008000]  'This takes whatever data you have in Combo_Country and puts it in the appropriate cell[/COLOR]
    lRow = lRow + 1
Next x

Edit: Sorry, I just skimmed over your code really quick and didn't realize you've got more than 1 worksheet you're working with. I'll change the code and try to get it to what you're looking for. Can I get a bit more information about the data you're working with? Like, the data in "A4" on your Engine sheet, will it always stay the same, or is it changing? Is it a formula? What generates it's value? Where you're doing your "1 To 9" loop, what sheet is that data being written to?