Completeing cell data 2

[UnsolvedCoding]

I have never had to code this before so if anyone knows how help would be greatly appreciated.

In the workbook there is a row of information that gets subtotatled by the years period. Beside that cell specific wording and formating must appear which is locked in because of downstream apps.

It should look like this in the cell:
Pre
12 Months
Expense
Export 254339

This sub will put that into a cell.

Sub Complete_Cell_Info()

ActiveCell.Formula = "Pre" & Chr(10) & "12 Months" & Chr(10) & "Expense" & Chr(10) & "Export 254339"

With ActiveCell.Characters(Start:=1, Length:=43).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 10
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

End Sub

However, because there are multipule possible expense locations the wording changes slightly and a constant doesn't seem to work.

Is there a way to get a constant to populate the cell or does it have to be broken out every time?

I tried this but VBA doesn't like it.

Const Expense = "Pre" & Chr(10) & "12 Months" & Chr(10) & "Expense" & Chr(10) & "Export 254339"

 

[SkipVought]

hi,

Where does Export [highlight]254339[/highlight] come from? Is it in some source data table somewhere?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

At this point, the code I am writing will input and change the export number based on what type of expense is determined.

Right now its being manually entered and is read down stream to indicate who gets the workbook and where the information is stored.

In other words the number changes based on the type of expense information given when the workbook is created.

 

[Andrzejek]

You may try:

Option Explicit[blue]
Private Const Expense as String = "Pre" & Chr(10) & _
"12 Months" & Chr(10) & "Expense" & Chr(10) & "Export 254339"[/blue]
[green]'Another Constant[/green]
[blue]Private Const ExpOther as String = "Post" & Chr(10) & _
"16 Months" & Chr(10) & "Other Expense" & Chr(10) & "Import 98765"[/blue]

Sub Complete_Cell_Info()

ActiveCell.Formula = [blue]Expense[/blue]

With ActiveCell.Characters(Start:=1, Length:=43).Font
    .Name = "Calibri"
    .FontStyle = "Regular"
    .Size = 10
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

End Sub

Have fun.

---- Andy

 

[UnsolvedCoding]

When it reads Chr(10) in the constant it kicks back an error.

Compile error:
Constant expression required

All that help does is tell me to initialize the constant.

 

[Andrzejek]

If "the number changes" - you can not use Const, you need a variable

Option Explicit
Dim Expense As String

...

Expense = "Pre" & Chr(10) & _
"12 Months" & Chr(10) & "Expense" & Chr(10) & _
"Export " & [blue]SomeNumber[/blue]

Sub Complete_Cell_Info()

ActiveCell.Formula = Expense
...

Have fun.

---- Andy

 

[UnsolvedCoding]

Still gives the same error

 

[UnsolvedCoding]

I tried using constants for each expression needed and I get the error message indicated. Making a variable and assigning it the string didn't work and the hybrid just suggested also gives the same error.

So far the only way it likes going into the cell is if its typed out as a cell value.

Example -

Range("K12").value = "Pre" & Chr(10) & "12 Months" & Chr(10) & "Expense" & Chr(10) & "Export 254339"

 

[SkipVought]

Forgetting about the stated problem, where does your data come from?

How do you know that it is for export 254339?

Are there other export values?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

When the code runs the only information to work with is what is in the workbook and the workbook name.

The workbook name gives the type of expense. To get that I have to do a Test = right(activeworkbook.name,28) then Test= Left(Test, 1). The character picked out indicates the type of expense. I can't go from the other end of the name because the last part of the workbook name changes.

Once opened the data in the workbook gets sorted, additional headers added, checked for date calculations and subtotaled. Once subtotaled the subtotal wording has to be isolated and in the cell beside the subtotal needs to go the wording indicate earlier.

Once the wording is added the workbook is saved under a different name and as a different workbook type.

To find the files a folder is opened with a scripting object and each file is pulled. This is a protected folder and only the files needed get put in this folder.

I don't know what happens downstream other then the wording is critical and I don't know how the workbooks get into the folder or who puts them there.

 

[SkipVought]

Expense = "Pre" & Chr(10) & _
"12 Months" & Chr(10) & "Expense" & Chr(10) & _
"Export " & left(right(activeworkbook.name,28),1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Thanks Skip