Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers 2

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.