Simple VBA Question

[Tompanilla]

Every day I recieve a report with data sorted in colums and rows.
I want to automatically mark the word "Sum:" in colums in Bold each time there is a sum.
The report is quite big so there is a sum calculation in different places all the time and several times in each report.
I also want the corresponding cells with sum amount to be in bold.
Eg. "A 43" Sum: "O43" The sum amount
"A74" Sum: "O74" The sum amount

As a new beginner in VBA I need some help.
Brgds

 

[Joe4]

Welcome to the Board!

If you just want to bold the entries that begin with "Sum", then VBA is not necessary, you can do it with Conditional Formatting.

Just use the "Formula Is" option,
use a formula like: =Left(A1,3)="Sum"
and choose Bold as your format option.

 

[Tompanilla]

The problem is still unsolved

The report I recieve every day is a report with about 1200 rows and 20 columns.
Every item in the report differs in place for every day. The only facts is that the word "Sum" is in column "A" and the amounts are in colums "O, P and Q".

For example. the report could look like this:

A B C D ..... O P Q
21 Sum 15,24 235,36 110,00 22564,10 3510,10 19044,00

53 Sum 10,12 200,10 214,00 22312,00 2310,00 20010,00


I have a macro that works in VBA to arrange all rows to have their specific size and to make the headers in row 1 to be in bold.
What I what to be done further in the report by the macro is to set all accounting rows with the word Sum to be in bold together with the amounts in rows O,P and Q. It should look like this:


A B C D ..... O P Q
21 Sum 15,24 235,36 110,00 22564,10 3510,10 19044,00 aso.

(A21, O21, P21 and Q21 Bold) the rest in normal formatting

I hope ther is a way to search for text in cells where the value is "Sum" and by that mark and make all those cells in bold by one macro.
The problem is to alsogo from the cell with the value "sum" and to the column with the amounts and also make them bold.

Something like this:
Find all cellvalues = Sum
also select same rows but only columns O,P and Q
make all those selections in bold.

Is there a solution?

 

[Joe4]

If I understand this correctly, is the word "Sum" appears in column A, you want columns A, O, P, and Q of that row bolded, right?

That still can be done without VBA using conditional formatting. Here is how:
1. Highlight column A (starting in Row 1 done to the bottom of the page).
2. Under Format, select Conditional Formatting.
3. Under Condition 1, choose the "Cell Value Is" option and enter this formula:
=NOT(ISERROR(FIND("Sum",$A1,1)))
4. Click on the Format tab, and select the Bold Font type and click OK.
5. Highlight columns O, P, and Q (starting in Row 1)
6. Repeat steps 2-4

That should do it!

 

[Tompanilla]

ADVERTISEMENT

It has to be done by Vba...

As I am leaving the company in some weeks I have to make it simple for my working mates as they dont understand macros, programming or VBA at all. But they would prefere that the reports looks nice from the printer every day.

My work just now is only temporarily as there is major problems to find a job here in Sweden. i have been unemployed for nearly 4 years so far. Before that i worked for American Express travel services in Sweden for nearly 20 years.

In my macro of today I have to add som rows to have the reports fixed in the future

 

[Joe4]

As I am leaving the company in some weeks I have to make it simple for my working mates as they dont understand macros, programming or VBA at all. But they would prefere that the reports looks nice from the printer every day.

I believe in a simple credo -- never make anything more complicated than it has to be. Taking something that Excel does inherently (like Conditional Formatting), and recreating it in in VBA is usually unnecessary, inefficient, requires extra resources, and extra maintenance. Once you set up Conditional Formatting, it works automatically without having to run any macros.

What reason does this have to be done in VBA versus Conditional Formatting? If it is a simplicity thing, Conditional Formatting is probably the way to go. If it absolutely has to be done in VBA, it can be. I just want to be certain that you are doing it for the right reasons.

 

[Tompanilla]

ADVERTISEMENT

Still problems

When I try the formula you wrote I recieve "Wrong in formula" and
"Sum",$A1,1 is marked in bold.


We recieve our reports every day by email as an attached excelfile.
This file is saved on the server each time and are to be modified by anyone of the staff who picks it up.
Therefore I have made a macro as shown below, and put it in an empty excelfile where it is made so it can be used on all open workbooks.

The person who recieves the report in mail stores it on the server and uses the macro to fix the report for printing purposes.
For the moment we have to manually mark all the words "Sum" and the corresponding calculations in columns O,P and Q and after that make all the selected things in bold.

I would like the macro to do this also so the report is ready for printing without any manually transactions.


My macro looks like this:

Sub Trafikrapport()
'
' Trafikrapport Makro
' Makrot inspelat 2005-09-14 av tn
'

'
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Rows("1:1").Select
Selection.Font.Bold = True
Range("F1").Select
ActiveCell.FormulaR1C1 = "Beräkn"
Range("R1").Select
ActiveCell.FormulaR1C1 = "6%"
Range("S1").Select
ActiveCell.FormulaR1C1 = "25%"
Columns("A:A").Select
Selection.ColumnWidth = 7.43
Columns("B:B").ColumnWidth = 3.57
Columns("C:C").ColumnWidth = 7.14
Columns("D:D").ColumnWidth = 24.86
Columns("E:E").ColumnWidth = 11.57
Columns("F:F").ColumnWidth = 5.86
Columns("G:G").ColumnWidth = 6.29
Columns("H:H").ColumnWidth = 7.14
Columns("I:I").ColumnWidth = 6.14
Columns("J:J").ColumnWidth = 6.14
Columns("K:K").ColumnWidth = 8.14
Columns("L:L").ColumnWidth = 5.86
Columns("M:M").ColumnWidth = 6.71
Columns("N:N").ColumnWidth = 6.14
Columns("O:O").ColumnWidth = 7
Columns("P:P").ColumnWidth = 7
Columns("Q:Q").ColumnWidth = 8.29
Columns("R:R").ColumnWidth = 5.14
Columns("S:S").ColumnWidth = 5.43
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("I:I").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("J:J").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("K:K").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("L:L").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("M:M").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("N:N").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("O:O").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("P:P").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("Q:Q").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("R:R").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("S:S").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.47244094488189)
.BottomMargin = Application.InchesToPoints(0.511811023622047)
.HeaderMargin = Application.InchesToPoints(0.354330708661417)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 92
End With
End Sub


Hope for help to fix the last programming in the macro.

Brgds

 

[Joe4]

Here is the section of code that you can add to your macro that will search column A, and if it finds the word "Sum" in column A, it will bold columns A, O, P, and Q of that row:

    Dim i As Long
    For i = 1 To Range("A65536").End(xlUp).Row
        If IsError(Application.Find("Sum", Cells(i, 1), 1)) Then
        Else
            Cells(i, 1).Font.Bold = True
            Range(Cells(i, 15), Cells(i, 17)).Font.Bold = True
        End If
    Next i

 

[Tompanilla]

New question

Thanks for your help!

I have a new question which I think you can help me to answer.

--
I have a form in an Excel spreadsheet , and on that form there is a combobox which I would like to fill with information from another worksheet.

At home I have Excel 2003 and can use Rowsourse to get the information from a specific worksheet. I have used Column A1:A25 in one worksheet named "set up", and named the selection staff (by Ctrl & F3).
When I type rowsourse sheetname+staff in Excel 2003 it works great and the combobox takes the staffnames from the set up sheet and puts them in a selection list in the combobox.

But at work, I have Excel 2000 and it is impossible to use rowsourse to anything else than cells on the selected workpage.
I recieve a notification that selection of rowsource on another page is illegal.

Is there a way to select my stafflist on workpage "set up" and use it in the combox in another workpage?

 

[Joe4]

I am not sure about that one (I only "dabble" with user forms).

If you have a new question (like you do here), it is best to start a new thread, so that everyone will take a look at it (threads with no replies get a lot more attention than thread with many replies).