Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was trying to make a quick tool today at work to automate some tasks at work but ran into the problem of getting Sun or Function error. I am an absolute newbie in VB and not familiar with the syntax and or language specifics. Could you please help me? Details on what I a trying to do: Basically, I have multiple groups(just 4 cells in each group) that I am trying to copy into single cell. Like this:

Group1: cell1 cell2 cell3 cell4

Group2: . . . Each one of those groups need to be copied into separate cells.

Here is the code:

Public Sub GlobalConcatenation()
    Dim sourcerange As Range
    Dim gbegin As Integer
    Dim gend As Integer
    gbegin = 2
    gend = 5
    sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbegin), Cells(2, gend))
    Dim i As Integer
    For i = 2 To 50
        callConcatinateAllCellValuesInRange (sourcerange)

        sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbegin + 4), Cells(2, gend + 4))
End Sub
Function ConcatinateAllCellValuesInRange(sourcerange As Excel.Range) As String
    Dim finalValue As String

    Dim cell As Excel.Range

    For Each cell In sourcerange.Cells
        finalValue = finalValue + CStr(cell.Value)
    Next cell

    ConcatinateAllCellValuesInRange = finalValue
End Function

EDIT: I feel like I also need to specify that the problem shows up in GlobalConcatenation() function and whenever I get the error, "sourcerange" is being highlighted.

EDIT: updated the code - fixed spelling mistake

share|improve this question
sourcerange appears in your code multiple times. So which line is execution actually breaking on? Also, there seem to be a few other typos/misspellings in your code. – Lèse majesté Apr 27 '12 at 6:45
up vote 0 down vote accepted

This is due to spell mistake on this line at gbeging:-

Wrong One:-

sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbeging), Cells(2, gend))

sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbeging + 4), Cells(2, gend + 4))

Correct One:-

sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbegin), Cells(2, gend))

sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbegin + 4), Cells(2, gend + 4))
share|improve this answer
thank you for the answer but the problem still persists. – GKED Apr 27 '12 at 6:22
@GKED: Now what the error message you are getting. Where you are assigning the value of callConcatinateAllCellValuesInRange (sourcerange)? – Siva Charan Apr 27 '12 at 6:29

Most likely cause of your error is the unqualified references to cells in the lines like

sourcerange = Sheets("raw_LSToolData").Range(Cells(2, gbegin), Cells(2, gend))

Cells(2, gbegin) refers to a cell on the active sheet. If this is not raw_LSToolData an error will result

The correct code is

sourcerange = Sheets("raw_LSToolData").Range(Sheets("raw_LSToolData").Cells(2, gbegin), Sheets("raw_LSToolData").Cells(2, gend))

A better way is

with Sheets("raw_LSToolData")
    sourcerange = .Range(.Cells(2, gbegin), .Cells(2, gend))
end with

Note the .'s before Range and Cells - this references the with clause object

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .