VBA excel help

[bbuk9999]

Hi everyone, I am trying to generate flexible numbers using top-up box and choosing numbers randomly from the generated numbers in VBA code. Thanks in advance.

[Paul_Hossler]

A little more information would be helpful

Maybe an example also?

Maybe some definitions: flexible, top-up box, choosing, ...

[bbuk9999]

Thanks Paul, I want to generate letters using input top-up box and these letters should be generated based on numbers we decide, for example, if I wanted to generate letters A & B, the number I decide will be 2, if I wanted to generate letters A, B & C the number will be 3. For each of these generated letters, should be generated letters based on number we decide, for example if I wanted to generate letters AV1, & AV2 the number will be 2, if I wanted to generate AV1, AV2, AV3 the number will be 3, the same for B and C.
I am attaching the exce file to show what I want. Thanks for your help.

[SamT]

What Programming language is "Top-Up Box" used in? Are you going to match the VBA to that language or do you need help with it also?

[bbuk9999]

Top-up box is used to insert the number to generate the letters. for example: if the inserted number is 2 so the generated letters will be A & B.

[Paul_Hossler]

I think the questions are: "In VBA terms or Excel terms, what is a 'top-up' box?"

[bbuk9999]

VBA terms, I mean by top-up box is something like this
LetterNum = InputBox ("How many letters?") , Thanks

[SamT]

Please use VBA and Excel terms. Made up words don't translate well and we have members all over the world. We all speak VBA and Excel but we don't all speak English.


LetterNum = InputBox ("How many letters?")
In VBA, that means Variable = Inputbox(Prompt), or just InputBox.

[Paul_Hossler]

VBA terms, I mean by top-up box is something like this
LetterNum = InputBox ("How many letters?") , Thanks

OK that helps a little

Even though 'InputBox' is sort of a popup, 'Top-up' is not really a standard term meaningful in VBA or Excel

Now, part 2 -- I think I sort of understand some of it, but what do you mean by 'flexible numbers' and in your example, there doesn't seem to be any 'random choosing'

I just looks like permutations of numbers and letters

Where does the 'V' come in?

16190

I think that to get more assistance, you're going to have to generate a very specific and very detailed step-by-step algorithm in words for what you're looking for

[bbuk9999]

I mean by flexible number whatever number you input using pop up box the letters will be generated, for example: if I input 3 using pop up box then the letters A, B, & C will be generated. After these letters are generated then I want to generate for each of these letters a number of Vs' , if I use another pop up box to generate Vs' , for example if I input 3 to generate 3 Vs' for A so AV1, AV2, & AV3 will be generated. If I input 2 to generate 2 Vs' for B so BV1, & BV2 will be generated. And if I input 4 to generate 4 Vs' for C then CV1, CV2, CV3, & CV4 will be generated. Thanks Paul.

[Paul_Hossler]

PLEASE use Excel or VBA terms (InputBox) instead of non-standard ones (pop up box)

This seems to be what I think I heard you say you wanted, but it is still not close to the original question


Hi everyone, I am trying to generate flexible numbers using top-up box and choosing numbers randomly from the generated numbers in VBA code. Thanks in advance.




Option Explicit

Sub LettersAndNumbers()

Dim iNumLetters As Long, iNumNumbers As Long, iLetters As Long, iNumbers As Long

iNumLetters = InputBox("How many letters", "Letters And Numbers")
iNumNumbers = InputBox("How many numbers", "Letters And Numbers")

If iNumLetters < 1 Or iNumNumbers < 1 Then Exit Sub

Application.ScreenUpdating = False

With ActiveSheet.Cells(1, 1)
For iLetters = 1 To iNumLetters
For iNumbers = 1 To iNumNumbers
.Offset(iNumbers, iLetters).Value = Chr(64 + iLetters) & "V" & iNumbers
Next iNumbers
Next iLetters
End With

Application.ScreenUpdating = True

End Sub

[bbuk9999]

[Solved=Paul_Hossler;343002] Thanks Paul, It works Perfectly.








Option Explicit

Sub LettersAndNumbers()

Dim iNumLetters As Long, iNumNumbers As Long, iLetters As Long, iNumbers As Long

iNumLetters = InputBox("How many letters", "Letters And Numbers")
iNumNumbers = InputBox("How many numbers", "Letters And Numbers")

If iNumLetters < 1 Or iNumNumbers < 1 Then Exit Sub

Application.ScreenUpdating = False

With ActiveSheet.Cells(1, 1)
For iLetters = 1 To iNumLetters
For iNumbers = 1 To iNumNumbers
.Offset(iNumbers, iLetters).Value = Chr(64 + iLetters) & "V" & iNumbers
Next iNumbers
Next iLetters
End With

Application.ScreenUpdating = True

End Sub

[/QUOTE]

[bbuk9999]

Thanks Paul, it works perfectly.

[bbuk9999]

Sorry Paul, there is something missing which is generating different numbers for each letter, for example: if A, B & C are generated, I want to generate AV1, AV2, BV1, BV2, BV3, CV1, CV2, CV3, CV4. Thanks

[Paul_Hossler]

1. Make sure the macro says:



.Offset(iNumbers, iLetters).Value = Chr(64 + iLetters) & "V" & iNumbers


I had something reversed when I copied the code. I edited the macro a minute after I posted the first version


2. Otherwise, it seems to generate your example in Post #3

Post #3
16195

My macro (after correction in 1 above)

16196

[bbuk9999]

Yes, that is correct. It is my mistake I thought I explained everything. Now I want to generate different numbers for each letter, for example: if A, B & C are generated, I want to generate AV1, AV2, BV1, BV2, BV3, CV1, CV2, CV3, CV4. Thanks Paul

[Paul_Hossler]

Now I want to generate different numbers for each letter, for example: if A, B & C are generated, I want to generate AV1, AV2, BV1, BV2, BV3, CV1, CV2, CV3, CV4.

Probably do-able, but I don't see how to take 3 letters and generate he permutations since I don't see the sense in the example:

3 Letters

'A' gets 2 numbers
'B' gets 3 numbers
'C' gets 4 numbers

[bbuk9999]

If you are able to do, please do that and I will be grateful. Thanks

[Paul_Hossler]

Option Explicit

Sub LettersAndNumbers()

Dim iNumLetters As Long, iLetters As Long, iNumbers As Long

iNumLetters = InputBox("How many letters", "Letters And Numbers")

If iNumLetters < 1 Then Exit Sub

Application.ScreenUpdating = False

With ActiveSheet.Cells(1, 1)
For iLetters = 1 To iNumLetters
For iNumbers = 1 To iLetters + 1
.Offset(iNumbers, iLetters).Value = Chr(64 + iLetters) & "V" & iNumbers
Next iNumbers
Next iLetters
End With

Application.ScreenUpdating = True

End Sub

[SamT]

Maybe what the OP wants is :

Letter = IputBox("Letter")
Number = InputBox("Number")
For i = 1 to Number
Result = Letter & "V" & i
Next

[bbuk9999]

Thanks Paul, but this is fixed, what I want is to generate Vs' for each letters with different numbers. For example: if letter A is generated then I want to generate 3 Vs' for A so AV1, AV2, AV3 will be generated. If I wanted to generate 4 Vs' for A, then AV1, AV2, AV3, AV4 will be generated. The same thing for the other generated letters. Thanks, I appreciate your assistance.

[Paul_Hossler]

I think the macro in post #11 did that



That seems different from your #16


Yes, that is correct. It is my mistake I thought I explained everything. Now I want to generate different numbers for each letter, for example: if A, B & C are generated, I want to generate AV1, AV2, BV1, BV2, BV3, CV1, CV2, CV3, CV4. Thanks Paul

But I'm glad you got something you're happy with

[bbuk9999]

The macro in post #11 gives the same Vs' number for each letter. What I want is to give different Vs' for each letter. and this difference depends on the number that I want to generate Vs' for each letter. For example: if I wanted to generate 3 Vs' for A so AV1, AV2 & AV3 will be generated, if I wanted to generate 5 V's for A so AV1, AV2, AV3, AV4, & AV5 will be generated. And the same thing for the other letters.