Solved: vba copy function

[lorraine]

Hello,

I would like to create a function that copy cell C1 into column B until ther is a value in column A.

I then stop copying, until it find a new vaue in column C.

To make it clearer I have attached a spreadsheet, sheet2 shows the desired results, sheet1 is the original spreadsheet.

I have tried to write te beginning of my fonction but i am not sure how to finish it and if it is right. Can you help me ? Thanks

What I have so far :


Function Copy (rng As Range)
Dim xl As Application, r1 As Range, r2 As Range, i As Integer
Set xl = Application
If IsEmpty(rng.Offset(-1)) Then
Set r1 = rng
Else
Set r1 = rng.End(xlUp)
End If
If IsEmpty(r1.Offset(1)) Then
Set r2 = r1
Else
Set r2 = r1.End(xlDown)

[mdmackillop]

Hi Lorainne,
Welcome to VBAX
Here are two slightly different versions. Look up SpecialCells in VBA Help to see how the initial items are found


Sub DoCopy()
Dim Rng As Range
Dim Cel As Range
Dim Tgt As Range

Set Rng = Columns("C:C").SpecialCells(xlCellTypeConstants, 2)
For Each Cel In Rng
Set Tgt = Range(Cel.Offset(, -2), Cel.Offset(, -2).End(xlDown)).Offset(, 1)
Tgt.Value = Cel.Value
Cel.Offset(, -1).ClearContents
Next
End Sub

Sub DoCopy2()
Dim Rng As Range
Dim Cel As Range

Set Rng = Columns("C:C").SpecialCells(xlCellTypeConstants, 2)
For Each Cel In Rng
i = 0
Do
i = i + 1
If Cells(Cel.Row + i, 1) <> "" Then
Cells(Cel.Row + i, 2) = Cel.Value
Else
Exit Do
End If
Loop
Next
End Sub

[mdmackillop]

BTW, You cannot use a function in this fashion. A function is used to return a value. It can be entered in a cell eg =SUM(A1:C1), but you cannot insert results into any other cell. For that you must use a sub routine

[lorraine]

thanks :content: !!!

Indeed you are right, i guess a function would not work in this case since it does not return a value but i just want to copy.

Nice website! Cheers.

[KK1966]

Hi, all

I have
curious if the VB code can chnage the object value before write in the targets cells, That's mean the language one

If Cells(cel.row+i, 1) <> "" then (this is copy from objects value "1000")
Cell(cel.row+i, 1) = Cel . Value ( Is it possible caonverts that to "10:00" before write in to cels )



Brother's


please advise and learn me about if it possible to do

[Bob Phillips]

If Cells(cel.row+i, 1) <> "" then
Cells(cel.row+i, 1) = Format(Cel.Value,"00:00")

[KK1966]

Dear XLD

I'm very happy you to learn me a much

Thanks