Solved: Range Concatenation

[stanl]

I'm just practicing here, because I am being asked to re-do several huge VBA modules.

Assume I create a looping sub


Sub test()
Dim i As Integer
For i = 0 To 5
n = printfunc(Sheets("sheet2"), CStr(i))
Next
End Sub


With my function as


Public Function printfunc(s As Worksheet, start As String) As Boolean
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = start
s.Select
r = Chr$(34) & "A" & start & Chr$(34)

'this fails
s.Range(r).Value = start

'this works
's.Range("A1").Value = start
End Function


My question is why doesn't the concatention work:banghead:

[geekgirlau]

Because there is no such cell as "A0"

[Charlize]

Public Function printfunc(s As Worksheet, start As String) As Boolean
s.select
'Sheets("Sheet1").Select
...
End Function

[stanl]

Because there is no such cell as "A0"

Missed that, but even making it For i=1 To 5 I still get a runtime error 1004 - Method 'Range' of Object _Worksheet Failed

Stan

[stanl]

Public Function printfunc(s As Worksheet, start As String) As Boolean

s.select
'Sheets("Sheet1").Select
...

End Function


I don't understand what you are trying to tell me:dunno

[moa]

You don't need the speech marks around "A" & start.

r = "A" & start

[Charlize]

Sub test()
Dim i As Long
Dim n
For i = 1 To 5
n = printfunc(Sheets(2), CStr(i))
Next
End Sub
Public Function printfunc(s As Worksheet, start As String) As Boolean
Dim r
'because you give the sheetsname when using the function printfunc
'you don't have to specify sheets(2). And start is 1 to 5
s.Select
Range("A1").Select
'A1 will be 1 to 5
ActiveCell.FormulaR1C1 = start
'store A + 1 to 5 in r
r = "A" & start
'column b will show A1 ... A5
s.Range(r).Offset(, 1).Value = r
End Function

[stanl]

You don't need the speech marks around "A" & start.

r = "A" & start


Thanks. I just tried .cells instead of .range which worked, then I tried something similar to what Charlize suggested and that worked and yours worked.

I inherited an undocumented workbook and it printed out 110 pages of macro code for Sheet1. I noticed that it was mostly repetitive blocks of the same code referencing a cell in Sheet1, updating its value from 0 to 51 then finding a corresponding range in another worksheet for printing (the range value of 0 is handled spearately). The whole process can probably be reduced to 3-4 pages with a function like printfunc() to concatenate parameters as a range.

I initially compiled 37 lines of Winbatch code which worked but WB cannot be used so I am slowly scraping the rust off my VBA. Thanks for your patience:friends: Stan

[moa]

You don't need to use .select either as you can use something like

Public Function printfunc(s As Worksheet, start As String) As Boolean
Sheets("Sheet1").range("A1").FormulaR1C1 = start
s.range("A" & start).Value = start
End Function

not tested

[mdmackillop]

Hi Stan,
Always remember to add Option Explicit to your code (and to the existing code you have to fix)

[Charlize]

Sub test()
Dim i As Long
'Dim n
For i = 1 To 5
'n = printfunc(Sheets(2), CStr(i))
Call printfunc(Sheets(2), Cstr(i))
Next
End Sub

[stanl]

Hi Stan,
Always remember to add Option Explicit to your code (and to the existing code you have to fix)

Finished... thanks all. 100 pages of code reduced to 6, and the people who will use it have a better understanding. My advice was beware of coders who charge by the line:bug: .... Stan