Excel 2003 VBA problem

  • Thread starter Thread starter BJ&theBear
  • Start date Start date
B

BJ&theBear

Never used R1C1 notation before and I;m not certain that I understand
it.

The routine below posts a copy of "ProjectID" to the next available
line but am unable to get the VBA macro to save with the R1C2 and R1C3
entries. VBA does not like this - all I was trying to do was post the
three input entries in the first available row in the first three
columns.

Can anyone help or at least point me in the right direction

Thanks

BJthebear

Sub InputNewproject()
'
' InputNewUser Macro
' Macro recorded 01/04/2010 by Brian
'
Dim NewprojectID As String 'ProjectID
Dim Newprojectname As String 'Projectname
Dim Newprojectdescription As String 'Projectdescription
Dim nextRow As Long

NewprojectID = Application.InputBox("Please enter New Project Number
(year first ie 1003)")
Newprojectname = Application.InputBox("Please enter New Project Name")
Newprojectdescription = Application.InputBox("Please enter details of
New Project")

Sheets("ProjectList").Select


'Find last row

Set SrcSht = Sheets("ProjectList")

nextRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

Application.ScreenUpdating = False


Range("A" & nextRow).Select

ActiveCell.FormulaR1C1 = NewprojectID
Selection.NumberFormat = "@" ' formats number as text

ActiveCell.FormulaR1C2 = Newprojectname
ActiveCell.FormulaR1C3 = Newprojectdescription

' Columns("A:C").Select
'Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
' DataOption1:=xlSortNormal
End Sub
 
..FormulaR1C1 reference style means that the formula you're going to apply will
be written in R1C1 reference style.

If you were doing this in excel (manually), it would mean that the A1 reference
style like:

=$z$99
would be written as:
=R99C26
(row 99, column 26)

But you have to tell excel that you're going to use this.

In xl2003 menus, it's:
Tools|Options|General Tab
(check R1C1 reference style)

Be aware that this is the setting that changes the letters above the columns
into numbers. (I usually hate this setting.)

===========
All that said...

There is no .FormulaR1C2 reference style. You're either using A1 (columns are
lettered) or R1C1 reference style (columns are numbered).

You can use either in your code and excel will display your formula to the user
based on their setting -- you (and your code) don't need to worry about it.

So I'm guessing that you want to put stuff in a cell, then more stuff in the
cell to its right and to its right and ...

With activecell
.formulaR1C1 = NewprojectID
.offset(0,1).formulaR1C1 = Newprojectname
.offset(0,2).formulaR1C1 = Newprojectdescription
End with

The .offset(0,#) says to stay on the same row (offset 0 rows) and the # is the
number of columns.

It looks like in your case that you're not actually dealing with formulas --
just populating those cells with your values.

I'd use:
With activecell
.value = NewprojectID
.offset(0,1).Value = Newprojectname
.offset(0,2).Value = Newprojectdescription
End with

========
Just a warning when/if you actually work with .Formula and .FormulaR1C1

If you used something like:
activecell.formulaR1C1 = "=$A$1+R99C26"
it should be an error. You're mixing the A1 and R1C1 reference style.

Either use:
activecell.formulaR1C1 = "=R1C1+R99C26"
or
activecell.formula = "=$A$1+$Z$99"
 
ActiveCell.Offset(,1).FormulaR1C1 = Newprojectname
ActiveCell.Offset(,2).FormulaR1C1 = Newprojectdescription
 
ActiveCell.Offset(,1).FormulaR1C1 = Newprojectname
ActiveCell.Offset(,2).FormulaR1C1 = Newprojectdescription

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


















- Show quoted text -
Click to expand...

Thank you both very much for your time and effort

Much appreciated

Bjthebear
 
Never used R1C1 notation before and I;m not certain that I understand
it.

The routine below posts a copy of "ProjectID" to the next available
line but am unable to get the VBA macro to save with the R1C2 and R1C3
entries. VBA does not like this - all I was trying to do was post the
three input entries in the first available row in the first three
columns.

Can anyone help or at least point me in the right direction

Thanks

BJthebear

Sub InputNewproject()
'
' InputNewUser Macro
' Macro recorded 01/04/2010 by Brian
'
Dim NewprojectID As String 'ProjectID
Dim Newprojectname As String 'Projectname
Dim Newprojectdescription As String 'Projectdescription
Dim nextRow As Long

NewprojectID = Application.InputBox("Please enter New Project Number
(year first ie 1003)")
Newprojectname = Application.InputBox("Please enter New Project Name")
Newprojectdescription = Application.InputBox("Please enter details of
New Project")

Sheets("ProjectList").Select


'Find last row

Set SrcSht = Sheets("ProjectList")

nextRow = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

Application.ScreenUpdating = False


Range("A"& nextRow).Select

ActiveCell.FormulaR1C1 = NewprojectID
Selection.NumberFormat = "@" ' formats number as text

ActiveCell.FormulaR1C2 = Newprojectname
ActiveCell.FormulaR1C3 = Newprojectdescription

' Columns("A:C").Select
'Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
' DataOption1:=xlSortNormal
End Sub
Click to expand...


Hi. Just to give some alternate ideas:

Cells(NextRow, 1) = NewprojectID
Cells(NextRow, 2) = Newprojectname
Cells(NextRow, 3) = Newprojectdescription

'or
Cells(NextRow, 1).Resize(1, 3) = _
Array(NewprojectID, Newprojectname, Newprojectdescription)


= = = = = = =
HTH :>)
Dana DeLouis
 
Back
Top