Selecting range from variables saved in a String (VBA in Excel)

Question

I am trying to select data between rows that the user inputs her/himself (As letters), I am also trying to limit the data so that it compares the values in one row and copies only the data with the same value in the chosen "compare column". Then it selects all the data with the same value in the "compare column" and saves it to a new sheet.

That is how it is supposed to work, however. I have been stuck for hours with the Range selection. I have saved the user input as strings, i.e if I want the data between ranges A3 to D19 it is saved as "A3" and "D19". When I then put it into a range:

Set selValue = arbEx.range(rangeStart & ":" & rangeStopp)

selValue.Copy

I get the "Application-defined or object-defined error". I have tried with countless different inputs for range, but I always get the same error. The code works if I comment the two above lines so the problems should be there.

Below is the full code,

Sub Lösning()

Set newWb = Workbooks.Add
'Set ActiveSheet = ActiveWorkbook.ActiveSheet


'Förhindrar screenflicker
Application.ScreenUpdating = False


'Skapar två sheets som används i programmet
Dim param As Worksheet
    Set param = ThisWorkbook.Sheets("Parametrar")
Dim arbEx As Worksheet
    Set arbEx = ThisWorkbook.Sheets("Arbets ex")

'Kod för att hämta startvädet av kolumn
Dim start As String
start = param.Cells(4, 3).Value

'Kod för att hämta slutvärdet av kolumn
Dim stopp As String
stopp = param.Cells(4, 4).Value

'Deklaration av variabler
Dim counter1 As Long
Dim counter2 As Long
Dim counter3 As Long
Dim rangeStart As String
Dim rangeStopp As String
Dim rangeFinal As String


'dela upp efter kolumn
Dim koluParam As String
Dim paramCheck As String

'Kolumn att dela upp efter
koluParam = param.Cells(4, 2).Value

'Algoritm för kopiering av valda kolumner
For i = 3 To 10000
paramCheck = arbEx.Cells(i, koluParam).Value
counter1 = counter1 + 1
counter2 = counter2 + 1
counter3 = counter3 + 1

    If (paramCheck <> arbEx.Cells((i + 1), koluParam).Value) Then
    counter3 = counter2 - counter1
    rangeStart = start & counter3
    rangeStopp = stopp & counter2

    '-------problem here
    Set selValue = arbEx.range(rangeStart & ":" & rangeStopp)
    selValue.Copy
    '-----------------

    newWb.Activate
    Sheets.Add
    ActiveSheet.Paste

    counter1 = 0
    End If

Next

'Rensar clipboard
Application.CutCopyMode = False

'Aktiverar uppdatering för att visa all data
Application.ScreenUpdating = True

End Sub

user3598756 · Accepted Answer · 2016-08-29 17:41:09Z

1

there's a bug in your code

the very first time you pass

If (paramCheck <> arbEx.Cells((i + 1), koluParam).Value) Then

then you set

counter3 = counter2 - counter1

which has counter2 and counter1 equal each other (to 1) thus returning 0, which leads to the error

you have to change the counterX setting

answered Aug 29, 2016 at 17:41

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ola Nilsson Over a year ago

Wow, I have spent all day looking at the range... Thank you, you just made my day!

user3598756 Over a year ago

You are welcome. When you're spending enough time struggling with the same issue, you take a rest (even some other coding) and/or ask for a 2nd pair of eyes...

Collectives™ on Stack Overflow

Selecting range from variables saved in a String (VBA in Excel)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related