Macro doesn't work using relative cells

[mdp76]

Hi there, I am trying the macros from the beginning of the book "VBA AND MACROS". When I tried to record the 2nd (Using relative references) and the 3rd (Not using AutoSum), the macros does not work, it load the invoice file but there is no formatting, and the "Design Mode" icon gets activated.
If I run the Macro using F8 from the VB editor the program runs the way its supposed to but it doesn't run using the shortcut.

Any help is appreciated.
Thank you

(Using Excel 2007)

 

[mdp76]

And it still isn't working?

Do this for me: paste the following code into your standard module and step through the first one with F8 so you're seeing each line executed to be certain it's running. Then go to a worksheet and press Ctrl-Shift-j and see if the message box comes up.

Sub setKeys2()
  Application.OnKey "^+{j}", "TestHotKeys"
End Sub
Sub TestHotKeys()
  MsgBox "Success!"
End Sub

I pasted the sub and was able to see the Message box ("Success")

 

[craig.penny]

There is hope!

Run this sub again:

Sub setKeys()
      Application.OnKey "^+{k}", "ImportInvNoAutoSum" 'ctrl-shift-k
End Sub

And then adjust your other code to include a msgbox like so:

Sub ImportInvNoAutoSum()
MsgBox "So far so good"
'
' ImportInvNoAutoSum Macro
'
' Keyboard Shortcut: Ctrl+Shift+K
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\QC\My Documents\PROGRAMMING\VBA+MACROS\VBA2010Files\invoice.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub

Then do what you did with the other code - step through the set keys sub, go to the worksheet and press Ctrl-Shift-k and see if you get the MsgBox. If you do then we can focus on the content of ImportInvNoAutoSum to see why it isn't doing what you expect it to.

 

[mdp76]

That worked too, I was able to see the message (So far so good)

but the fields are still not formatted, the program works if I don't use relative references though, I am not sure if I mentioned that for this macro I used the Macro recorder.

Thanks!

 

[craig.penny]

Is it still the case that the code does what you expect when you step through it with F8 but not when you use Ctrl-Shift-k?

 

[craig.penny]

I just spent some time looking at the main body of your code but I don't know what's it's doing exactly because of the relative reference style. Of course relative reference is very useful but in this case I would suggest you use absolute references so that I can better understand what you're doing -or- copy the important part of the worksheet and paste it here.

Which cell is selected before that code runs?

 

[mdp76]

Is it still the case that the code does what you expect when you step through it with F8 but not when you use Ctrl-Shift-k?

Yes, same thing it works using F8 from the VB editor

 

[mdp76]

This is the data running the macro using absolute references:

InvoiceDate InvoiceNumber SalesRepNumber CustomerNumber ProductRevenue ServiceRevenue ProductCost
07/06/2008 123829 S21 C8754 538400 0 299897
07/06/2008 123830 S45 C4056 588600 0 307563
07/06/2008 123831 S54 C8323 882200 0 521726
07/06/2008 123832 S21 C6026 830900 0 494831
07/06/2008 123833 S45 C3025 673600 0 374953
07/06/2008 123834 S54 C8663 966300 0 528575
07/06/2008 123835 S21 C1508 467100 0 257942
07/06/2008 123836 S45 C7366 658500 10000 308719
07/06/2008 123837 S54 C4533 191700 0 109534
Total 5797300 10000 3203740

The totals are for ProdRevenue, Service Rev and ProductCost

Thanks again, I really appreciate your help

 

[craig.penny]

Run this and tell me what the message box says.

Sub ImportInvNoAutoSum()
'
' ImportInvNoAutoSum Macro
'
' Keyboard Shortcut: Ctrl+Shift+K
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\QC\My Documents\PROGRAMMING\VBA+MACROS\VBA2010Files\invoice.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
Selection.End(xlDown).Select
MsgBox ActiveCell.Address
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub

 

[mdp76]

Run this and tell me what the message box says.

Sub ImportInvNoAutoSum()
'
' ImportInvNoAutoSum Macro
'
' Keyboard Shortcut: Ctrl+Shift+K
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\QC\My Documents\PROGRAMMING\VBA+MACROS\VBA2010Files\invoice.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
Selection.End(xlDown).Select
MsgBox ActiveCell.Address
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub

I only see the message box if I run the macro with F8.
the message box says: "$A$18"
(Different invoice file with more data)