Minimizing number of commands

N

N.F

Hello Happy Thursday!! to everyone.
Any help is deeply appreciated. I got two questions:

1) I have pasted part of my code below:

Sub GenerateTextFile()
Dim Coll As Integer ' output column index
Dim HeaderRow As Long
Dim Heading1 As String
Dim Heading2 As String

HeaderRow = Range("Header").Row ' get location of header row.
Heading1 = Sheets("Sheet1").Cells(HeaderRow, 2) ' get headings
Heading2 = Sheets("Sheet1").Cells(HeaderRow, 3)

Sheets("Output").Cells.ClearContents ' clear output sheet
Roww = HeaderRow + 1 ' first row
i = 1
Do
Coll = 1
Sheets("Output").Cells(i, Coll) = "&D" ' First field

If Sheets("Sheet1").Cells(Roww, 2) <> "" Then
Coll = Coll + 1 ' bump column index
Sheets("Output").Cells(i, Coll) = " " & Heading1 & "=" &
Sheets("Sheet1").Cells(Roww, 2) & "." ' First field
End If
If Sheets("Sheet1").Cells(Roww, 3) <> "" Then
Coll = Coll + 1 ' bump column index
Sheets("Output").Cells(i, Coll) = " " & Heading2 & "=" &
Sheets("Sheet1").Cells(Roww, 3) & "." ' First field
End If

Coll = Coll + 1 ' bump column index
Sheets("Output").Cells(i, Coll) = "/" '
last slash


Roww = Roww + 1 ' next row
i = i + 1
Loop While Sheets("Sheet1").Cells(Roww, 1) <> ""
End Sub


The problem is I actually have 60 heading commands, 60 If statemets etc. on
the code
How can I write this code in a more compact way??

2) My other question: When I execute the above macro, it generates me a
table with the corresponding headings and a given value under those headings:
EXAMPLE

HEADING1='title1.' HEADING2= 55. HEADING3=46. /

HEADING4='title2.' HEADING5=66. HEADING2=56. /

etc............
etc............
etc............

I want it to display it as is with the only exception that when the user
types in a value in Sheet 1 under a given heading, if this particular value
has a decimal in it such as 2.33 then I want to display it in Sheet
("Output") as 2.33 and not 2.33. ( with only the first decimal and not the
second) But if the user types in a value under a heading without a decimal
then I would like to display it in Sheet("Output") as it is right now with a
decimal at the end.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top