[SOLVED] macro for Text Generator based of fields

[adamheon]

I am trying to create a macro to apply to a button that will automatically generate 1 line of text from multiple lines.

so to try and better explain this, i will have several options that need to be either selected from a drop down or entered as text (or #). Then i want to combine all that information and have it become 1 line of text.

example:

question 1 - xyz
question 2 - abc
question 3 - 123

click generate button

specific cell = xyz, abc - 123

attached is the actual information and additional notes in red that i would like to see. hopefully this helps. any help would be much appreciated!! EXAMPLE.xlsxEXAMPLE.xlsx

[patel45]

you don't need vba or button, put in B5 this formula

= "(PAR) " & G5 & " " & G6 & " " & G7 & " - (" & G8 & ")" & G9 & "+(" & G10 & ")" & G11 & "+(" & G12 & ")" & G13 &"  X " &G14

in C5

="MARK:" & G4

[adamheon]

im looking for the button becuase i want to be able to generate multiple "orders" or lines from the same information area. So basically i would like to enter the info needed, click the button, have it clear the information entered and generate a single line of text. Then i can repeat the process and click the button and it will add a new line under the previous one. Does that make sense?

[patel45]

Sub a()
LR = Cells(Rows.Count, "B").End(xlUp).Row + 1
Range("B" & LR) = "(PAR) " & Range("G5") & " " & Range("G6") & _
" " & Range("G7") & " - (" & Range("G8") & ")" & Range("G9") & _
"+(" & Range("G10") & ")" & Range("G11") & "+(" & Range("G12") & _
")" & Range("G13") & "  X " & Range("G14")
Range("C" & LR) = "MARK:" & Range("G4")
End Sub

[adamheon]

As i'm putting this into my actual sheet i came up with one error.

i need the generated text to start at cell D4839 and then continue from there. below are the changes i made, the first line has the error.

Sub GENERATE()
LR = Cells(Rows.Count, "D4839").End(xlUp).Row + 1
Range("D4839" & LR) = "(PAR) " & Range("Q4842") & " " & Range("Q4843") & _
" " & Range("Q4844") & " - (" & Range("Q4845") & ")" & Range("Q4846") & _
"+(" & Range("Q4847") & ")" & Range("Q4848") & "+(" & Range("Q4849") & _
")" & Range("Q4850") & "  X " & Range("Q4851")
Range("F4839" & LR) = "MARK:" & Range("Q4841")
End Sub

[patel45]

Sub GENERATE()
LR = Cells(Rows.Count, "D").End(xlUp).Row + 1
Range("D" & LR) = "(PAR) " & Range("Q4842") & " " & Range("Q4843") & _
" " & Range("Q4844") & " - (" & Range("Q4845") & ")" & Range("Q4846") & _
"+(" & Range("Q4847") & ")" & Range("Q4848") & "+(" & Range("Q4849") & _
")" & Range("Q4850") & "  X " & Range("Q4851")
Range("F" & LR) = "MARK:" & Range("Q4841")
End Sub

cell D4838 must be not empty

[adamheon]

That worked perfect! Thank you! The only other thing I was hoping the button would do is also clear the data in cells G4-G14 when clicked.

[adamheon]

the problem there are other areas in column D that have text. So this wants insert the text down at the bottom of the spreadsheet. Is there a way to have it insert in a specific cell? (D4839)

[patel45]

yes, but need a service cell (for example X1) for saving the last row number, only the first time you need D4839

Sub GENERATE()
LR = Range("X1") ' put 4839
Range("D" & LR) = "(PAR) " & Range("Q4842") & " " & Range("Q4843") & _
" " & Range("Q4844") & " - (" & Range("Q4845") & ")" & Range("Q4846") & _
"+(" & Range("Q4847") & ")" & Range("Q4848") & "+(" & Range("Q4849") & _
")" & Range("Q4850") & "  X " & Range("Q4851")
Range("F" & LR) = "MARK:" & Range("Q4841")
Range("X1").value = Range("X1").value +1
End Sub

[adamheon]

Sorry i'm not great at vba. So with the code you posted, you're saying that i need a service cell (example x1). Not sure what a service cell is, but in the attached example i have multiple lines of text (labeled ORDER and test) in column B. however i need to new text to generate under the word ORDER not under the last row of text. I'm sure you have answered this but i'm unclear as to how to modify the code. If you could provide a sample code for this example i think i could figure it out. thanks for your help!

EXAMPLE.xlsm

[adamheon]

never mind i was able to figure out what you were saying. Everything works perfectly! Thank you for your help!

[patel45]

great ! but you have to put [SOLVED] in the title of the first post, not in the current

[adamheon]

all set put in under the first post. thanks again.