CODEWITHSUNDEEP
excelvba
Valkeif
Valkeif

Reputation: 29

How to find a value in column and paste ranges from other worksheets into its adjacent columns

The end goal for my project is that the user will be able to select a value from a ComboBox to fill out a report on a Summary Tab. The report will consist of 3, 3 cell ranges (divided into 3 1x3 ranges on 3 separate worksheets).

I want to find the row with the value the user selected in the ComboBox and then set the 9 cells to the right of that value equal to the values in the range mentioned previously.

I've tried a couple of different ways of doing this, but I'll include the code I most recently worked on below:

Private Sub OKButton1_Click()
Dim userValue, rangeOne, rangeTwo, rangeThree
Dim i As Long

i = 4


userValue = ComboBox1.Value
Set rangeOne = Sheets("Sheet2").Range(Range("F23:H23")
Set rangeTwo = Sheets("Sheet3").Range("F90:H90")
Set rangeThree = Sheets("Sheet4").Range("F17:H17")



While Sheets("Reports").Range(cells(i,1)).Value <> "" 
      If Sheets("Reports").Range(cells(i, "A")).Value = "userValue" Then

         Set Sheets("Reports").Range(Cells(i, "B:E")) = rangeOne
         Set Sheets("Reports").Range(Cells(i, "F:I")) = rangeOne
         Set Sheets("Reports").Range(Cells(i, "J:M")) = rangeOne
      End If
      i = i + 1
Wend
Unload UserForm2
End Sub

Any Ideas on how I can improve this or get it working? Currently getting 1004 errors.

Upvotes: 2

Views: 108

Answers (2)

Valkeif
Valkeif

Reputation: 29

For x = 1 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
    If InStr(1, Cells(x, 1), UserValue) > 0 Then
        ws1.Cells(x, 2) = ws2.Cells(23, 6).Value
        ws1.Cells(x, 3) = ws2.Cells(23, 7).Value
        ws1.Cells(x, 4) = ws2.Cells(23, 8).Value

        ws1.Cells(x, 6) = ws3.Cells(90, 6).Value
        ws1.Cells(x, 7) = ws3.Cells(90, 7).Value
        ws1.Cells(x, 8) = ws3.Cells(90, 8).Value

        ws1.Cells(x, 10) = ws4.Cells(18, 6).Value
        ws1.Cells(x, 11) = ws4.Cells(18, 7).Value
        ws1.Cells(x, 12) = ws4.Cells(18, 8).Value
    Else:
    End If
Next x

The above is what I'm working with now in place of the while loop.

Upvotes: 0

Lord Elrond
Lord Elrond

Reputation: 16032

Two words of advice when working with excel:

  1. always make variables for each sheet/book you need to work with
  2. Avoid using ranges and objects if you can. It is much easier to iterate over individual cells using an array and a for loop like I did below.

I was a bit confused on exactly what you needed done, so you will need to modify this slightly to fit your ranges/where you want the data to go. If you are confused or need further assistance let me know and I'll update this.

Dim userValue
Dim xrow As Long, ws1 As Worksheet, ws2 As Worksheet, ws3 as Worksheet, ws4 as Worksheet
Dim arrData() as variant

set ws1 = Worksheets("Report")
set ws2 = Worksheets("Sheet2")
set ws3 = Worksheets("Sheet3")
set ws4 = Worksheets("Sheet4")

userValue = ComboBox1.Value
xrow = 1

ws2.activate
'the InStr function checks if the first condition contains the second, and when it does, it returns 1, which in turn triggers the if statement
for x = 1 To ws2.Cells(rows.count, 1).end(xlup).row
    if InStr(1, Cells(x, 1), userValue) > 0 Then
        arrData(0) = ws2.Cells(x, 2).value
        arrData(1) = ws2.Cells(x, 3).value
        arrData(2) = ws2.Cells(x, 4).value
    else:
    end if
next x

ws3.activate
for x = 1 To ws3.Cells(rows.count, 1).end(xlup).row
    if InStr(1, Cells(x, 1), userValue) > 0 Then
        arrData(3) = ws3.Cells(x, 2).value
        arrData(4) = ws3.Cells(x, 3).value
        arrData(5) = ws3.Cells(x, 4).value
    else:
    end if
next x

ws4.activate
for x = 1 To ws4.Cells(rows.count, 1).end(xlup).row
    if InStr(1, Cells(x, 1), userValue) > 0 Then
        arrData(6) = ws4.Cells(x, 2).value
        arrData(7) = ws4.Cells(x, 3).value
        arrData(8) = ws4.Cells(x, 4).value
    else:
    end if
next x

ws1.activate
ws1.Cells(xrow, 1) = userValue
for y = 0 To 8
    ws1.Cells(xrow, y+1).value = arrData(y)
next y
xrow = xrow + 1

Upvotes: 1

Related Questions