0

I created an excel sheet to merge 3 columns into 1 string. The code should produce a string like this:

"A"; B; C; 0.000; 14.1; 4.1; 14.1; ""; 0.00; -3.1; ""; 0.00; ""; 1; 0.500; 0.000; 0.000; 0; 0.05

At its current state, the code works just fine it just that I'd like to make it a little bit more tidy and functional.

Sub WP2_Maker()
'
' Simple_WP2_Maker Macro
'

For i = 20 To Cells(Rows.Count, "B").End(xlUp).Row
Cells(i, "E").Value = Chr(34) & Cells(i, "A").Value & Chr(34) & "; " & Cells(i, "B").Value & "; " & Cells(i, "C").Value & "; " & "0.000" & "; " & "14.1" & "; " & "4.1" & "; " & "14.1" & "; " & Chr(34) & Chr(34) & "; " & "0.00" & "; " & "-3.1" & "; " & Chr(34) & Chr(34) & "; " & "0.00" & "; " & Chr(34) & Chr(34) & "; " & "1" & "; " & "0.500" & "; " & "0.000" & "; " & "0.000" & "; " & "0" & "; " & "0.05"

Next i
End Sub

Basically I have 3 columns A, B and C and when clicked generate, it populate row E with the combined string.

For now, it does the job fine but there are more features that I'd like to add.

  1. I added clear all button to clear the sheet for next input, because now even though I clear cell A, B, C for new input, I still need to clear cell E too. how can I make column E to automatically cleared upon clicking generate button when cells in B and/or C are blank? Saw this workaround but not sure how to implement it in my code. (Determine when two consecutive cells are blank)
  2. How to create a save button to save the generated string into a text format without messing with the result? I'd tried save it as tab delimited format but that added extra quotes in the strings.
  3. I wanted to have a drop down list/combo box showing options like circle (-2.1), small circle (-3.1) and depending on which option selected change the values in the code respectively. How can I achieve this?

Thank you.

Astronyu
  • 61
  • 2
  • 4

1 Answers1

0

I suggest to create column E via a formula like (E20):

=IF(LEN(A20&B20&C20),""""&A20&"""; "&B20&"; "&C20&"; 0.000; 14.1; 4.1; 14.1; """"; 0.00; -3.1; """"; 0.00; """"; 1; 0.500; 0.000; 0.000; 0; 0.05","")

If you want to change the 3.1 to something different via dropdown simply change the formula (assuming the dropdown at E1):

=IF(LEN(A20&B20&C20),""""&A20&"""; "&B20&"; "&C20&"; 0.000; 14.1; 4.1; 14.1; """"; 0.00; "&E1&"; """"; 0.00; """"; 1; 0.500; 0.000; 0.000; 0; 0.05","")

Simply copy the formula down as you like to (if A, B and C are empty, the formula shows an empty string)

And then run the code to populate.

To save column E (20 to last entry) the way you want, you can do it via Open For Output:

Option Explicit

Public Sub to_text()

  Dim i As Long

  Open "C:\OutputTestFile.txt" For Output As #1

    For i = 20 To Columns(5).Find("*", Cells(1, 5), xlValues, , , 2).Row
      Print #1, Cells(i, 5).Value
    Next i

  Close 1#

End Sub

Using the formula, column E will be automatically empty when clearing contents for A:C.

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • Thanks Dirk, your suggestion works too but I'm looking to code it in vba instead. Will try the save to text solution. – Astronyu Jul 10 '17 at 11:19
  • Save column E works. How can I make it to ask for a file name and where to save instead? Something like msoFileDialogFolderPicker – Astronyu Jul 10 '17 at 23:05
  • You can do it like [THIS](https://stackoverflow.com/questions/26717371/getting-the-initial-path-of-msofiledialogfolderpicker) to get a path and then simply open an `InputBox` for the filename... – Dirk Reichel Jul 11 '17 at 09:24