Combobox/userform

[Albatross]

Small problem...


On a worksheet, I have a collection of data sorted by rows. I built a userform with a combobox that looks up to the range of data.

Waht I'm trying to get it to do, is with a commandbutton, the use the selection from the combobox, to filter the range of data, copy it, and paste it into the same sheet.

The range of data I placed to the far right starting at cell ab1 and want the paste to start at cell a1. (Reason for putting the source data so far away it that it doesn't need to be seen by everyone.) My range of data is ab1:ak700

So what im looking for is code that will capture my selection from combobox1, and when commandbutton1 on the same userform is pressed, filter the range of ab1:ak700 based on the selection made in combobox1, copy the result, and paste values to a1.

I've got everything built but for some reason, I can't seem to get it to work. (Got ticked and deleted the code and have been rebuilding from scratch with the same criteria)

Any help, code, ideas, notions, or beverages of the calming kind would be greatly helpful!

[Siddharth Rout]

for the time being lets say the range is k7:k12 in lieu of ab1:ak700 in sheet1 and you want the data to be pasted in column "A" in sheet2

lets say k7="apple",k8="orange", k9="grapes",k10="orange",k11="apple",k12="grapes"

i believe u want to use the combobox to select from the list "apple,orange,grapes" and paste the data in cell a1 in sheet2 when the command button is pressed?
is that correct? if yes paste the below code in the combobox code.(define a list for the combobox before running the code)

checkone=7 '7 because the range starts at k7, u can select your own number
checktwo=1 '1 because the range starts at a1 in sheet2, u can select your own number
 
dim flagone as boolean
 
with worksheets("sheet1").range("k7:k13")
  do
    yyy="k"+format(checkone) 
    ' "k" because that is the column where the data is stored
    ' If checkone =7 then the value of yyy becomes k7
 
    zzz="a"+format(checktwo)
    ' "a" because that is the column and checktwo is the cell no in that column in sheet2
    'where u need to paste the data. if checktwo =1 then the value of zzz becomes a1
 
    if combobox1.value=range(yyy).value then
      worksheets("sheet2").range(zzz).value=combobox1.value
      checkone=checkone+1
      checktwo=checktwo+1
    else
      checkone=checkone+1
    endif
    
    if checkone=13 then '13 because the range ends at k12, u can select your own number
       flagone=false
    end if
   
    loop until flagone=false
endwith

if you select orange from the list then the values of a1 and a2 in sheet2 changes to "orange" as there are only two that match the criteria...

Hope this helps...