Sif has attended:
Excel Advanced course
Creating loops/dynamic ranges
Hi,
I am trying to generate sentences using VBA. I am using a For Next loop and the decision codes If then Else. I have got the loop going for the 52 variables I want the sentence for, but my range and the rows that I want to concatenate are not dynamic....
The code I have written (apologies this is my first macro)
Sub SentenceGenerator()
Dim OneValue As String
Dim TwoValue As String
Dim ThreeValue As String
Dim FourValue As String
Dim NillValue As String
Dim mRange As Integer
For i = 1 To 52
mRange = Range("L3")
If mRange = "1" Then
ActiveCell.Offset(0, 10).Select
ActiveCell = OneValue
OneValue = [B3] & [C3] & [D3] & [G3] & [E3] & [H3] & [I3] & [K3] & [M3] & [N3] & [O3] & [Q3] & [U3]
ElseIf mRange = "2" Then
ActiveCell.Select
TwoValue = [B3] & [C3] & [D3] & [G3] & [E3] & [H3] & [I3] & [K3] & [M3] & [N3] & [O3] & [Q3] & [P3] & [R3] & [U3]
ActiveCell = TwoValue
ElseIf mRange = "3" Then
ActiveCell.Select
ThreeValue = [B3] & [C3] & [D3] & [G3] & [E3] & [H3] & [I3] & [K3] & [M3] & [N3] & [O3] & [Q3] & ", " & [R3] & [P3] & [S3] & [U3]
ActiveCell = ThreeValue
ElseIf mRange = "4" Then
ActiveCell.Select
FourValue = [B3] & [C3] & [D3] & [G3] & [E3] & [H3] & [I3] & [K3] & [M3] & [N3] & [O3] & [Q3] & ", " & [R3] & ", " & [S3] & [P3] & [T3] & [U3]
ActiveCell = FourValue
ElseIf mRange = "0" Then
ActiveCell.Select
NillValue = "Has shown no improvement in any category in the past five years"
ActiveCell = NillValue
Else
End If
ActiveCell.Offset(1, 0).Select
Next i
End Sub
My question: How can I get my range and the defined String variables to be 'dynamic'? By this I mean I want the row to change in both, so L3 --> L4 --> L5, etc. as well as [B3] & [C3] & [D3] & [G3] & [E3] & [H3] & [I3] & [K3] & [M3] & [N3] & [O3] & [Q3] & ", " & [R3] & ", " & [S3] & [P3] & [T3] & [U3] --> [B4] & [C4] & [D4] & [G4] & [E4] & [H4] & [I4] & [K4] & [M4] & [N4] & [O4] & [Q4] & ", " & [R4] & ", " & [S4] & [P4] & [T4] & [U4]
Thank you very much for any help in advance!!
RE: Creating loops/dynamic ranges
Hi Sif
Sorry for not getting back sooner.
You've done well for your first macro! Very creative to use a macro as a sentence generator.
The only thing I can see stopping your code working as you want is the line
mRange = Range("L3")
It needs to be before the For loop begins. If you move it to the start immediately after the Dim statements it should work dynamically as you want.
Let me know if there is anything else needed to make it work. For example to have the sentences appear on separate lines?
Regards
Doug
Best STL
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.