Need macro code for filling values in Sheet 2 by taking input parameters from Sheet1

**ashaanna88** · 09-30-2012, 10:56 AM

Hi All,

I am new to macro.

I need a help for the below scenario.
When we enter the value for the Account number and Service number in Sheet 1, a random number should be generated in Sheet 2 correspondingly.

PFA the excel showing an example.

The values in the first row for both Account number and Service number can be random values(can be Integer or String) and the rows below can be incremented.

Please help me in this regards and let me know if any more input from my side is required.

TIA.

Regards,
Asha

**mike7952** · 09-30-2012, 11:23 AM

Give this a try

Sub TestMacro()
 Const cAcct As String = "Acct"
 Const cSnbr As String = "SNbr_Acc"
 Dim accNum As Long, n As Long
 Dim serNum As Long
 Dim arrResults()
 
 With Worksheets("Sheet1")
    If Not IsNumeric(.Cells(1, "C")) And Not IsNumeric(.Cells(2, "C")) Then Exit Sub
    ReDim arrResults(1 To Rows.Count, 1 To 2)
    For accNum = 1 To .Cells(1, "C")
       For serNum = 1 To .Cells(2, "C")
           n = n + 1
           arrResults(n, 1) = cAcct & accNum
           arrResults(n, 2) = cSnbr & accNum & "_" & serNum
       Next
    Next
 End With
 Sheet2.Range("a1").Resize(n, 2) = arrResults
End Sub

**ashaanna88** · 10-01-2012, 12:25 AM

Thanks Mike for this help !!

This code is working fine when I create a New Macro project. But when i try to merge it with my project, its showing 'Run time error:1004' at Sheet2.Range("a1").Resize(n, 2) = arrResults

One more thing,
I needed random values for Account Number and Service numbers. It should not be a Constant value.
And I prefer Integer values to String for both Account Number and Service Number.

PFA the example i am Searching for.
Thanks a lot for providing this help !!

Regards,
Asha

**mike7952** · 10-01-2012, 09:32 AM

Change

Sheet2.Range("a1").Resize(n, 2) = arrResults

to: Replace NameOfDestSheet to you sheet name

Worksheets("NameOfDestSheet").Range("a1").Resize(n, 2) = arrResults

Need macro code for filling values in Sheet 2 by taking input parameters from Sheet1

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need macro code for filling values in Sheet 2 by taking input parameters from Sheet1

Re: Need macro code for filling values in Sheet 2 by taking input parameters from Sheet1

Re: Need macro code for filling values in Sheet 2 by taking input parameters from Sheet1

Re: Need macro code for filling values in Sheet 2 by taking input parameters from Sheet1

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions