PDA

View Full Version : Alternative To ActiveCell.Row



IcePirates
01-13-2009, 11:02 AM
Hello,

Is there an alternative to ActiveCell.Row, in some cases I might need the data from more than just one cell...So if I use
ActiveCell.Row, 2

Well that is useful if I just want that cell...But what if I need to select several cells...and not just one...?

Any alternative...?
My Code now:
" Persons Name:" & wks.Cells(ActiveCell.Row, 17) & vbNewLine & vbNewLine & _

Kenneth Hobs
01-13-2009, 11:18 AM
Type the word Range in the VBE and press F1. Range has several forms. One is to set the first cell, a comma, and then the seconnd cell would be like A1:B10. e.g.
Dim r as Range
Set r=Range("A1:B10")
'or
Set r=Range("A1", "B10")

Bob Phillips
01-13-2009, 11:27 AM
How would the data from more than one row manifest? If concatenating, just use



With wks.Cells(ActiveCell.Row, 17)

" Persons Name:" & .Value & " " & .Offset(1,0)Value & vbNewLine & vbNewLine & _

...
End With

IcePirates
01-13-2009, 12:15 PM
Hello,

Thanks for the responses...

This here:
With wks.Cells(ActiveCell.Row, 17)
" Persons Name:" & .Value & " " & .Offset(1,0)Value & vbNewLine & vbNewLine & _
...
End With

I had to alter to fit into my code to this:
"Persons Name:" & wks.Cells(ActiveCell.Row, 17) & .Value & " " & .Offset(1,0)Value & vbNewLine & vbNewLine & _

But, VBE, just returns a 'Syntax Error', doesn't tell me where or what...
Any suggestions?

IcePirates
01-13-2009, 12:16 PM
Type the word Range in the VBE and press F1. Range has several forms. One is to set the first cell, a comma, and then the seconnd cell would be like A1:B10. e.g.
Dim r as Range
Set r=Range("A1:B10")
'or
Set r=Range("A1", "B10")


I tried this method, but when I specify the range - VBE says, "Expected: Seperator, ) or semi-colon."

And it wont accept that..If you have another suggestion though, please let me know!

Kenneth Hobs
01-13-2009, 12:32 PM
If you can't do that then you have serious problems. Test it on a new instance of Excel in a new workbook.

In the Immediate window, you can see the returned addresses.
Sub test()
Dim r As Range
Set r = Range("A1:B10")
Debug.Print r.Address
'or
Set r = Range("B5", "W51")
Debug.Print r.Address
End Sub

IcePirates
01-13-2009, 12:56 PM
Ok,

That worked when I tried it in a new instance of Excel, but it didn't work in the script Im working on...In column C a user can select what options they want, by double-clicking in the cell (a check mark will apear)

My biggest problem is, I cannot get the information the user selects to transfer from the excel sheet, to the word document...I attached an example using your method Kenneth...

If you look at sheet1 in the VBE...I have all the necessary coding there for the check marks, it should just be a matter of altering module1

Let me know your thoughts on how I can improve this!

Thanks

Kenneth Hobs
01-13-2009, 02:24 PM
Not sure what part of that has to do with what you asked here.

There are other ways to do your task. I prefer to create the basic DOC files as DOT files and then I use FormFields, Bookmarks or delimited marked text and do a find/replace.

Even a one record MailMerge is easily done. Record it in MSWord if you want to try that method. The tricky part is to set a new data source and sql.

For one thing, you have a syntax error for one long string. Rather than making one big string, use the string concatenation method as you did for other parts. Or, use a method like this. You can use just one vbCrLf if you don't want blank lines between the text lines.
Dim s() As String, sString As String
ReDim s(1 To 500) As String
s(1) = "Hello World!"
s(2) = "How are things going?"
s(3) = "I hope that all is well."
ReDim Preserve s(1 To 3) As String
sString = Join(s, vbCrLf & vbCrLf)
wdRng = sString


At the end, add:
Set wdApp = Nothing