copying if value in cell from range of cells
 

I am looking vor the visual basic code to perform the following action.
I have column "a" with data in every cell, columns "j, k and l 'may have
data in them.

If there is data in j, k or l I want to copy the data from "a" into another
worksheet in column "a", then the data in j, k or l in either b, c or d on
the other worksheet on the same row number as the information in column a.
Since column a has over 120 rows I am looking for a simple solution to this.
Any help in pointing me in the right direction would be helpful.

copying if value in cell from range of cells
 

Sub movedata()

RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & RowCount) < ""
If .Range("J" & RowCount) < "" Or _
.Range("K" & RowCount) < "" Or _
.Range("L" & RowCount) < "" Then

ColA = .Range("A" & RowCount)
ColJ = .Range("J" & RowCount)
ColK = .Range("K" & RowCount)
ColL = .Range("L" & RowCount)

With Sheets("Sheet2")
.Range("A" & RowCount) = ColA
.Range("J" & RowCount) = ColJ
.Range("K" & RowCount) = ColK
.Range("L" & RowCount) = ColL

End With

End If
RowCount = RowCount + 1
Loop
End With

End Sub


"thomsonpa" wrote:

I am looking vor the visual basic code to perform the following action.
I have column "a" with data in every cell, columns "j, k and l 'may have
data in them.

If there is data in j, k or l I want to copy the data from "a" into another
worksheet in column "a", then the data in j, k or l in either b, c or d on
the other worksheet on the same row number as the information in column a.
Since column a has over 120 rows I am looking for a simple solution to this.
Any help in pointing me in the right direction would be helpful.

copying if value in cell from range of cells
 

Try this:

Sub test()
hitcounter = 1
endA = Range("A" & Rows.Count).End(xlUp).Row
For Each acell In Range("A2:A" & endA)
If Not IsEmpty(Range("J" & acell.Row)) Or _
Not IsEmpty(Range("K" & acell.Row)) Or _
Not IsEmpty(Range("L" & acell.Row)) Then
hitcounter = hitcounter + 1
Worksheets("othersheet").Range("A" & hitcounter).Value =
acell.Value
Worksheets("othersheet").Range("B" & hitcounter).Value =
Range("J" & acell.Row).Value
Worksheets("othersheet").Range("C" & hitcounter).Value =
Range("K" & acell.Row).Value
Worksheets("othersheet").Range("D" & hitcounter).Value =
Range("L" & acell.Row).Value
End If
Next acell
End Sub

Regards,
Stefi

€žthomsonpa€� ezt Ã*rta:

I am looking vor the visual basic code to perform the following action.
I have column "a" with data in every cell, columns "j, k and l 'may have
data in them.

If there is data in j, k or l I want to copy the data from "a" into another
worksheet in column "a", then the data in j, k or l in either b, c or d on
the other worksheet on the same row number as the information in column a.
Since column a has over 120 rows I am looking for a simple solution to this.
Any help in pointing me in the right direction would be helpful.

copying if value in cell from range of cells
 

Sub copyrangeif()'Execute from Source sheet
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
With Sheets("yourdestinationsheetname")
lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
'MsgBox lr
If Len(Trim(Cells(i, "a"))) 0 _
And Application.CountA(Range(Cells(i, "j"), Cells(i, "l"))) 0 Then
'MsgBox i
Cells(i, "a").Copy .Cells(lr, "a")
Range(Cells(i, "j"), Cells(i, "l")).Copy .Cells(lr, "b")
End If
End With
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"thomsonpa" wrote in message
...
I am looking vor the visual basic code to perform the following action.
I have column "a" with data in every cell, columns "j, k and l 'may have
data in them.

If there is data in j, k or l I want to copy the data from "a" into
another
worksheet in column "a", then the data in j, k or l in either b, c or d on
the other worksheet on the same row number as the information in column a.
Since column a has over 120 rows I am looking for a simple solution to
this.
Any help in pointing me in the right direction would be helpful.

copying if value in cell from range of cells
 

Many thanks, works perfectly. What would I do if it wasnt for this community.

"Stefi" wrote:

Try this:

Sub test()
hitcounter = 1
endA = Range("A" & Rows.Count).End(xlUp).Row
For Each acell In Range("A2:A" & endA)
If Not IsEmpty(Range("J" & acell.Row)) Or _
Not IsEmpty(Range("K" & acell.Row)) Or _
Not IsEmpty(Range("L" & acell.Row)) Then
hitcounter = hitcounter + 1
Worksheets("othersheet").Range("A" & hitcounter).Value =
acell.Value
Worksheets("othersheet").Range("B" & hitcounter).Value =
Range("J" & acell.Row).Value
Worksheets("othersheet").Range("C" & hitcounter).Value =
Range("K" & acell.Row).Value
Worksheets("othersheet").Range("D" & hitcounter).Value =
Range("L" & acell.Row).Value
End If
Next acell
End Sub

Regards,
Stefi

€žthomsonpa€� ezt Ã*rta:

I am looking vor the visual basic code to perform the following action.
I have column "a" with data in every cell, columns "j, k and l 'may have
data in them.

If there is data in j, k or l I want to copy the data from "a" into another
worksheet in column "a", then the data in j, k or l in either b, c or d on
the other worksheet on the same row number as the information in column a.
Since column a has over 120 rows I am looking for a simple solution to this.
Any help in pointing me in the right direction would be helpful.