PDA

View Full Version : Worksheet to find values of a sheet and copy the column



MarceloTDK
02-13-2014, 04:00 PM
Hello there,

I've been working on a worksheet for days now without the expected results. The idea is to have a command button that runs a macro that finds the value "r" by columns on the Sheet 2, and when found this value, copy only the names corresponding the row that the value was found (Column A) to Sheet 1 column A


In case that no values are found, copy only the names corresponding to the rows that have no values, to Sheet 1 column B. Please refer to the table below for references of the worksheet.

A E F G H I













Joćo



r






carlos










bryan




r

r



tommy










aaron



r
r
r




tj






r



hank



r






walt



r
r


r


gus



r






mike



r

r
r



saul










haas




r





almeida






r



callegaro



r

r




tiago







r




I've created a VBA code to find the value "r" and copy its address to Sheet 1. The problem is that this code does not run as it should, probably because it's missing something. I haven't been successful with finding the columns with no values and copying them to Sheet 1. I believe that a variant must be put into the code in order to store information, but i am stuck with this problem.

Please refer to the code that i wrote, below for references:

Private Sub CommandButton1_Click()

Dim c As Range
Dim firstaddress As String

With Sheets("plan2").Range("E2:I16")

Set c = .Find(What:="r", LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, Searchformat:=False)

firstaddress = c.Address

If Not c Is Nothing Then

Do

Range(c.Address).Select
Selection.Copy
Sheets("plan1").Select
Range("A2").Select
Sheets("Plan1").Paste

Set c = .FindNext(c)

Loop While c Is Nothing

End If

End With

End Sub



I would like your sugestions regarding this problem that i am facing. I am really stuck here! I would really appreciate any help that i could get!

Thank you so much for your attention,

Marcelo

mancubus
02-13-2014, 11:14 PM
hi. welcome to VBAX. check this out for find method.: http://msdn.microsoft.com/en-us/library/office/ff839746.aspx ----- you dont have to select objects to work with them:
c.Copy Destination:=Sheets("Plan1").Range("A2") even Destination:= bit may be omitted: c.Copy Sheets("Plan1").Range("A2") ----- you can attach your sample workbook. click Go Advanced button / scroll down / click manage attachments.