Using code to rename a cell

[Graham C1600]

Hi,
I inherited a spreadsheet which uses code to extract data from an MPP plan and import into Excel. It works fine but just needs a couple of little tweaks.

Firstly there is some code to rename the Excel Spreadsheet which is as follows :-
Else
Sheet.Range("B3").Value2 = "Technical Implementation Plan" + Trim(Worksheets("Control Panel").Range("E5").Value2)
Sheet.Range("B2").Value2="Galaxy Programme"

I want this code to put into B3 the words "Technical Implementation Plan" then add what it says in cell H6 on the same sheet.

Secondly there is some code to change the colour of some fonts which looks like this :-
Else
If Task.Text20 = "Black" Then
Sheet.Cells (i, 8).Font.ColorIndex = 1

This is all working fine but is there a way to instead of change the colour of some text, actually fill in the cell where the text is ?

Hope this all makes sens.

Thanks

 

[HaHoBe]

Hi Graham,

the changes may look like these:

'...
Else
  With Sheet
    .Range("B3").Value2 = "Technical Implementation Plan" & Trim(.Range("H6").Value2)
    .Range("B2").Value2 = "Galaxy Programme"
  End With
'...

and

'...
Else
  If Task.Text20 = "Black" Then
    Sheet.Cells(i, 8).Interior.Color = vbBlack
'...

You would need to change the colour of the font to some light colour in case you want to use black as background.

Ciao,
Holger

 

[Graham C1600]

Hi Graham,

the changes may look like these:

'...
Else
  With Sheet
    .Range("B3").Value2 = "Technical Implementation Plan" & Trim(.Range("H6").Value2)
    .Range("B2").Value2 = "Galaxy Programme"
  End With
'...

and

'...
Else
  If Task.Text20 = "Black" Then
    Sheet.Cells(i, 8).Interior.Color = vbBlack
'...

You would need to change the colour of the font to some light colour in case you want to use black as background.

Ciao,
Holger

Hi,

The code to change the colour works fine.

I'm getting an error on this one :-
With Sheet
.Range("B3").Value2 = "Technical Implementation Plan" & Trim(.Range("H6").Value2)
.Range("B2").Value2 = "Galaxy Programme"
End With

When I try to run it I get a Compile Error : Block If without End If. It then highlights an End Sub on the page with loads of other code.

Any ideas how to resolve this ? Hope it's simple.

Thanks

 

[HaHoBe]

Hi Graham C1600,

the compiler states that you started an If-Clause, maybe added an Else or ElseIf but didn´t put in an End If for that clause. If the code worked before maybe you deleted that statement when copying in the code.

If you can´t put in the code here for us to look at it you should try and make good use of indention of the code in order to make it obvious which parts oif the code belong together.

Sorry but can´t help without looking at the entire code.
Holger

 

[Graham C1600]

Hi Graham C1600,

the compiler states that you started an If-Clause, maybe added an Else or ElseIf but didn´t put in an End If for that clause. If the code worked before maybe you deleted that statement when copying in the code.

If you can´t put in the code here for us to look at it you should try and make good use of indention of the code in order to make it obvious which parts oif the code belong together.

Sorry but can´t help without looking at the entire code.
Holger

I'm unable to copy all the code but the section giving errors is as follows :-
If Backout Than
Sheet.Range ("B3").value2 = "Backout Plan"
Sheet.Range ("B2"). value2 = "Galaxy"
Else
With Sheet
.Range("B3").value2 = "Technical Implementation Plan - " + Trim(.range("H6").value2)
.Range("B2").value2 = "Galaxy"
End with

This is giving the following error - Compile Error Invalid or unqualified reference and highlights this from the code (in red)- Trim(.range("H6").value2)

 

[HaHoBe]

Hi Graham C1600,

from the sniplet you posted you would need to add an End If at the very end of the code

If Backout Than
  With Sheet
    .Range("B3").Value2 = "Backout Plan"
    .Range("B2").Value2 = "Galaxy"
  End With
Else
  With Sheet
    .Range("B3").Value2 = "Technical Implementation Plan - " + Trim(.Range("H6").Value2)
    .Range("B2").Value2 = "Galaxy"
  End With
End If

HTH
Holger

 

[HaHoBe]

Since this code only relies to Sheet you may also alter the code to read

With Sheet
  If Backout Than
    .Range("B3").Value2 = "Backout Plan"
    .Range("B2").Value2 = "Galaxy"
  Else
    .Range("B3").Value2 = "Technical Implementation Plan - " + Trim(.Range("H6").Value2)
    .Range("B2").Value2 = "Galaxy"
  End If
End With

And as Range("B2") will be the same value you may put that line directly under With Sheet and delete both of the lines in If..Else..End If.

Ciao,
Holger

 

[Graham C1600]

Since this code only relies to Sheet you may also alter the code to read

With Sheet
  If Backout Than
    .Range("B3").Value2 = "Backout Plan"
    .Range("B2").Value2 = "Galaxy"
  Else
    .Range("B3").Value2 = "Technical Implementation Plan - " + Trim(.Range("H6").Value2)
    .Range("B2").Value2 = "Galaxy"
  End If
End With

And as Range("B2") will be the same value you may put that line directly under With Sheet and delete both of the lines in If..Else..End If.

Ciao,
Holger

Hi,
Works a treat so thanks for that. Last query on this.
So I'm using this - .Range("B3").Value2 = "Technical Implementation Plan - " + Trim(.Range("H6").Value2) + Trim(.Range("H7").Value2).
This gives me back sausagebacon.
My question is how do I get it to display sausage - bacon ? How do I insert the -

Thanks

 

[HaHoBe]

Hi Graham C1600,

when I´m working with Strings I use the ampersand and when adding numbers the plus sign. VBA can handle the plus sign to concatenate strings as well...

Add a string as a divider between the values of the cells like

.Range("B3").Value2 = "Technical Implementation Plan - " & Trim(.Range("H6").Value2) & " - " & Trim(.Range("H7").Value2)

Ciao,
Holger

 

[Graham C1600]

Hi Graham C1600,

when I´m working with Strings I use the ampersand and when adding numbers the plus sign. VBA can handle the plus sign to concatenate strings as well...

Add a string as a divider between the values of the cells like

.Range("B3").Value2 = "Technical Implementation Plan - " & Trim(.Range("H6").Value2) & " - " & Trim(.Range("H7").Value2)

Ciao,
Holger

Many thanks for all your help.