Help to add Format to VBA Code!

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

--
After searching Google.groups.com and finding no answer, Bob Vance asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
 
Activecell.numberformat = "mm/dd/yyyy"
(use the format of your choice)
--
After searching Google.groups.com and finding no answer, Bob Vance asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
 
If you really meant copy|paste special|Formats, you could record a macro to see
the general shape of the code.

I got something like this when I chose two cells and did a copy|paste
special|formats

Range("C2").Copy
Range("D2").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False



Dave said:
Activecell.numberformat = "mm/dd/yyyy"
(use the format of your choice)
--
After searching Google.groups.com and finding no answer, Bob Vance asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
 
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub
 
Tom, that doesn't work, it only produces the numbers 38210, the cell is not
formatted for the date that's why I wanted to bring the format with it,
because I use the cell as a general number cell when I don't need the month
put in it, Thanks Bob


Tom Ogilvy said:
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy


Bob said:
--
After searching Google.groups.com and finding no answer, Bob Vance asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
 
Can I get the result in Bold:

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30" (Bold)

End With
End Sub
TIA Bob

Bob said:
Tom, that doesn't work, it only produces the numbers 38210, the cell is not
formatted for the date that's why I wanted to bring the format with it,
because I use the cell as a general number cell when I don't need the month
put in it, Thanks Bob


Tom Ogilvy said:
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy


Bob said:
--
After searching Google.groups.com and finding no answer, Bob Vance asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
The way your code is written says that you only select that cell if the
topleftcell.column > 8.

Otherwise, you just plop the formula into the activecell. Is that what you
really meant?

If no, maybe:

Option Explicit

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(ActiveCell.Row - 2, ActiveCell.Column + 30).Copy
ActiveCell.PasteSpecial xlFormats
ActiveCell.Font.Bold = True
End If
End With
End Sub

It only puts the formula, copies/pastes the formatting and apply bold if that
topleftcell.column is > 8.


Can I get the result in Bold:

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30" (Bold)

End With
End Sub
TIA Bob

Bob said:
Tom, that doesn't work, it only produces the numbers 38210, the cell is not
formatted for the date that's why I wanted to bring the format with it,
because I use the cell as a general number cell when I don't need the month
put in it, Thanks Bob


Tom Ogilvy said:
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy




--
After searching Google.groups.com and finding no answer, Bob Vance asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
J163 has the formula =X1 which is the date
When I use your Code it selects J165 which is correct then enters this
formula in it: =J163+30 and shows this value 38210 in Bold
TIA Bob

Dave Peterson said:
The way your code is written says that you only select that cell if the
topleftcell.column > 8.

Otherwise, you just plop the formula into the activecell. Is that what you
really meant?

If no, maybe:

Option Explicit

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(ActiveCell.Row - 2, ActiveCell.Column + 30).Copy
ActiveCell.PasteSpecial xlFormats
ActiveCell.Font.Bold = True
End If
End With
End Sub

It only puts the formula, copies/pastes the formatting and apply bold if that
topleftcell.column is > 8.


Can I get the result in Bold:

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30" (Bold)

End With
End Sub
TIA Bob

Bob said:
Tom, that doesn't work, it only produces the numbers 38210, the cell
Click to expand...
is
not
formatted for the date that's why I wanted to bring the format with it,
because I use the cell as a general number cell when I don't need the month
put in it, Thanks Bob


Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy




--
After searching Google.groups.com and finding no answer, Bob Vance
asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
Ah, +30 means to add 30 to the value--not to the column number!

Option Explicit
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
'this line selects J165
Cells(.Row - 43, .Column - 8).Select

'this line puts the formula in J165
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

'this line copies the cell two rows up in the same column
'note the change (I removed +30)!
Cells(ActiveCell.Row - 2, ActiveCell.Column).Copy

'pastes just the formats
ActiveCell.PasteSpecial xlFormats

'bolds the cell
ActiveCell.Font.Bold = True
End If
End With
End Sub
J163 has the formula =X1 which is the date
When I use your Code it selects J165 which is correct then enters this
formula in it: =J163+30 and shows this value 38210 in Bold
TIA Bob

Dave Peterson said:
The way your code is written says that you only select that cell if the
topleftcell.column > 8.

Otherwise, you just plop the formula into the activecell. Is that what you
really meant?

If no, maybe:

Option Explicit

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(ActiveCell.Row - 2, ActiveCell.Column + 30).Copy
ActiveCell.PasteSpecial xlFormats
ActiveCell.Font.Bold = True
End If
End With
End Sub

It only puts the formula, copies/pastes the formatting and apply bold if that
topleftcell.column is > 8.


Can I get the result in Bold:

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30" (Bold)

End With
End Sub
TIA Bob

Tom, that doesn't work, it only produces the numbers 38210, the cell is
not
formatted for the date that's why I wanted to bring the format with it,
because I use the cell as a general number cell when I don't need the
month
put in it, Thanks Bob


Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy




--
After searching Google.groups.com and finding no answer, Bob Vance
asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
Brilliant Dave :), what if I wanted the cell to the right (K165) to do the
same thing as J165 , so effectively K163 get taken done to K165 and has 30
added as well....TIA Bob

Dave Peterson said:
Ah, +30 means to add 30 to the value--not to the column number!

Option Explicit
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
'this line selects J165
Cells(.Row - 43, .Column - 8).Select

'this line puts the formula in J165
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

'this line copies the cell two rows up in the same column
'note the change (I removed +30)!
Cells(ActiveCell.Row - 2, ActiveCell.Column).Copy

'pastes just the formats
ActiveCell.PasteSpecial xlFormats

'bolds the cell
ActiveCell.Font.Bold = True
End If
End With
End Sub
J163 has the formula =X1 which is the date
When I use your Code it selects J165 which is correct then enters this
formula in it: =J163+30 and shows this value 38210 in Bold
TIA Bob

Dave Peterson said:
The way your code is written says that you only select that cell if the
topleftcell.column > 8.

Otherwise, you just plop the formula into the activecell. Is that
Click to expand...
what
you
really meant?

If no, maybe:

Option Explicit

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(ActiveCell.Row - 2, ActiveCell.Column + 30).Copy
ActiveCell.PasteSpecial xlFormats
ActiveCell.Font.Bold = True
End If
End With
End Sub

It only puts the formula, copies/pastes the formatting and apply bold
Click to expand...
if
that
topleftcell.column is > 8.



Bob wrote:

Can I get the result in Bold:

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30" (Bold)

End With
End Sub
TIA Bob

Tom, that doesn't work, it only produces the numbers 38210, the
Click to expand...
cell
is
not
formatted for the date that's why I wanted to bring the format
Click to expand...
with
it,
because I use the cell as a general number cell when I don't need the
month
put in it, Thanks Bob


Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy




--
After searching Google.groups.com and finding no answer, Bob Vance
asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 
Still based off the click of the same button???

Option Explicit
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
Cells(.Row - 43, .Column - 8).Select
ActiveCell.Resize(1, 2).FormulaR1C1 = "=R[-2]C+30"
Cells(ActiveCell.Row - 2, ActiveCell.Column).Resize(1, 2).Copy
ActiveCell.PasteSpecial xlFormats
ActiveCell.Resize(1, 2).Font.Bold = True
End If
End With
End Sub

Since you're working with adjacent cells, you can just fill in both cells with
the common formula and copy both cells and paste|special still one time.

The .resize(1,2), says to make the range 1 row by 2 columns.

(and I'd didn't need to resize the .pastespecial line--xl will match the
"sending" range's size.


Brilliant Dave :), what if I wanted the cell to the right (K165) to do the
same thing as J165 , so effectively K163 get taken done to K165 and has 30
added as well....TIA Bob

Dave Peterson said:
Ah, +30 means to add 30 to the value--not to the column number!

Option Explicit
Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
'this line selects J165
Cells(.Row - 43, .Column - 8).Select

'this line puts the formula in J165
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

'this line copies the cell two rows up in the same column
'note the change (I removed +30)!
Cells(ActiveCell.Row - 2, ActiveCell.Column).Copy

'pastes just the formats
ActiveCell.PasteSpecial xlFormats

'bolds the cell
ActiveCell.Font.Bold = True
End If
End With
End Sub
J163 has the formula =X1 which is the date
When I use your Code it selects J165 which is correct then enters this
formula in it: =J163+30 and shows this value 38210 in Bold
TIA Bob

The way your code is written says that you only select that cell if the
topleftcell.column > 8.

Otherwise, you just plop the formula into the activecell. Is that what
you
really meant?

If no, maybe:

Option Explicit

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then
Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(ActiveCell.Row - 2, ActiveCell.Column + 30).Copy
ActiveCell.PasteSpecial xlFormats
ActiveCell.Font.Bold = True
End If
End With
End Sub

It only puts the formula, copies/pastes the formatting and apply bold if
that
topleftcell.column is > 8.



Bob wrote:

Can I get the result in Bold:

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30" (Bold)

End With
End Sub
TIA Bob

Tom, that doesn't work, it only produces the numbers 38210, the cell
is
not
formatted for the date that's why I wanted to bring the format with
it,
because I use the cell as a general number cell when I don't need the
month
put in it, Thanks Bob


Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"
Cells(Activecell.row-2,ActiveCell.Column+30).Copy
ActiveCell.PasteSpecial xlFormats
End With
End Sub

--
Regards,
Tom Ogilvy




--
After searching Google.groups.com and finding no answer, Bob Vance
asked:
Can "CopyFormat" be added to this code

Sub MonthPlus()
With ActiveSheet.Buttons(Application.Caller).TopLeftCell
If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select
ActiveCell.FormulaR1C1 = "=R[-2]C+30"

End With
End Sub

Thanks in advance.........Bob Vance
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Slight change needed for Code! 3
How can I put these 2 VBA codes Together? 1
Slight change needed for VBA Code 6
Whats wrong with this code? 1
Who can join these two VBA Codes together? 4
Slight change needed for my script 2
VBA Code to have User input data for cell 1
Amendment need to Macro ! 5

Back
Top