VBA to generate data with certain parameters.

[madhu_61244]

Hello Gurus,


I wonder how we can generate the data in a spreadsheet with certain parameters. Here is the scenario. So far I'm creating this data by dragging the cursor. I've Model in column A and serial number (you can call it as VIN) in Column B as shown below.

Model - VIN

ABC1001 VINSN2300001

ABC1001 VINSN2300002

ABC1001 VINSN2300003

Column A holds the same data. where as column B always incremented by 1 (or by any other number. But it is consistent).


What I'm expecting - If I give the Model, Serial number range (last seven digits of VIN - in my example it is from 2300001 to 2300003), VIN Pattern(number or characters prior to 7 digit s/n. in my example it is VINSN) and increment (in my example it is just 1) by excel has to create the data in spread sheet.


Is that something we can do it by using VBA.


Thanks for your help in advance.


Thanks,

Madhu

 

[Satish.digit]

Sub model()

Dim aRange As Range

Dim i As Long, l As Long

'Dim LR As Long

On Error Resume Next

Set aRange = Application.InputBox(Prompt:="Enter range", Type:=8)

If aRange Is Nothing Then

MsgBox "Operation Cancelled"

Else

aRange.Value = Application.InputBox(Prompt:="Enter Model")


End If

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

If Cells(i, "A").Value <> "" Then

Cells(i, "B").Value = i + 2299999

End If

Next i

Do While ActiveCell <> ""


ActiveCell.Offset(0, 1).FormulaR1C1 = "VINSN" & ActiveCell.Offset(0, 0)


ActiveCell.Offset(1, 0).Select

Loop


End Sub


How to use the macro in Microsoft Office Excel 2007/2010

1.Open the workbook that contains the data.

2.Press ALT+F11 to start the Visual Basic Editor.

3.On the Insert menu, click Module to insert a module. Type the macro in the module's code window.

4.On the File menu, click Close and Return to Microsoft Excel.

5.Select the worksheet that contains the data that you want to concatenate.

6.Click the top cell in the right column of data that you want to concatenate. For example, if cells A1:A100 and B1:B100 contain data, click cell B1.

7.Click the Developer tab. If the Developer tab is not displayed, follow these steps:

a.Click the Microsoft Office Button, and then click Excel Options.

b.Click Popular.

c.Click to select the Show Developer tab in the Ribbon check box.

d.Click OK to close the Excel Options dialog box.

8.Click Macros in the Code group.

9.Select the ConcatColumns macro, and then click Run.


Thanks,

Satish.

 

[Satish.digit]

Hi Madhu,


Please find the updated code:


Sub model()

Dim aRange As Range

Dim i As Long


On Error Resume Next

Set aRange = Application.InputBox(Prompt:="Enter range", Type:=8)

If aRange Is Nothing Then

MsgBox "Operation Cancelled"

Else

aRange.Value = Application.InputBox(Prompt:="Enter Model")


End If

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

If Cells(i, "A").Value <> "" Then

Cells(i, "B").Value = i + 2299999

End If

Next i

Do While ActiveCell <> ""


ActiveCell.Offset(0, 1).FormulaR1C1 = "VINSN" & ActiveCell.Offset(0, 0)


ActiveCell.Offset(1, 0).Select

Loop


End Sub

 

[madhu_61244]

Satish,


Kudos to you. I tried that and it worked. you provided very detailed description on enabling developer tab in the menu. It helped a lot. Otherwise it would be of no use.


Is there any way that we can create a form to enter all these details instead of entering those in multiple input boxes. Is that something we can do? If yes, I'll take help from my IT team to get that done.


Once again, thanks for your help.


Happy Thanksgiving,


Thanks,

Madhu R