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