VBA- copy and paste

[Nani520]

Hi. I'm just new to VBA and I'm just wondering if someone can help me in writing a macro code for below:

If in Column A contains the word "Value", it would be replaced by the cell's value (Acc) in sheet 2.

Thank you in advance.

Sheet1

Sheet2

 

[Zot]

What is the relationship between Value cell in column A Sheet1 and the Acc location in F Sheet2?

 

[Nani520]

What is the relationship between Value cell in column A Sheet1 and the Acc location in F Sheet2?

i just want to change the word "value" to the number under Acc. For eg, first value change to 456, 2nd value change to 123 then third value change to 789.

 

[Zot]

i just want to change the word "value" to the number under Acc. For eg, first value change to 456, 2nd value change to 123 then third value change to 789.

The value is in column F or can be anywhere? What is the starting row for these value under Acc?

The word Value in Sheet1 can be many and equal number of rows to numbers in Acc?

 

[Nani520]

The value is in column F or can be anywhere? What is the starting row for these value under Acc?

The word Value in Sheet1 can be many and equal number of rows to numbers in Acc?

value in Column F starts row 1.
The word value in sheet1 would be equal to the number of rows to numbers in Acc.

 

[Zot]

I'm assuming all on the same sheet

Sub Test()

Dim n As Long
Dim cell As Range, rngData As Range

Set rngData = Range("A1", Cells(Rows.Count, "A").End(xlUp))

n = 1
For Each cell In rngData
    If cell = "Value" Then
        n = n + 1
        cell = Range("F" & n)
    End If
Next

End Sub

 

[Nani520]

I'm assuming all on the same sheet

Sub Test()

Dim n As Long
Dim cell As Range, rngData As Range

Set rngData = Range("A1", Cells(Rows.Count, "A").End(xlUp))

n = 1
For Each cell In rngData
    If cell = "Value" Then
        n = n + 1
        cell = Range("F" & n)
    End If
Next

End Sub

I have tried to run with this VBA, but only the first value has been changed and it changed to the last number (789) instead of the first number (456).

 

[Zot]

I have tried to run with this VBA, but only the first value has been changed and it changed to the last number (789) instead of the first number (456).

I cannot think how could that happened. I have actually tried the code. Is this what you wanted?

Before

Book1
	A	B	C	D	E	F
1	Value					Acc
2	1					456
3	2					123
4	3					789
5	4
6	5
7
8	Value
9	1
10	2
11	3
12	4
13	5
14
15	Value
16	1
17	2
18	3
19	4
20	5
Sheet1

After

Book1
	A	B	C	D	E	F
1	456					Acc
2	1					456
3	2					123
4	3					789
5	4
6	5
7
8	123
9	1
10	2
11	3
12	4
13	5
14
15	789
16	1
17	2
18	3
19	4
20	5
Sheet1

 

[Nani520]

I cannot think how could that happened. I have actually tried the code. Is this what you wanted?

Before

Book1
	A	B	C	D	E	F
1	Value					Acc
2	1					456
3	2					123
4	3					789
5	4
6	5
7
8	Value
9	1
10	2
11	3
12	4
13	5
14
15	Value
16	1
17	2
18	3
19	4
20	5
Sheet1

After

Book1
	A	B	C	D	E	F
1	456					Acc
2	1					456
3	2					123
4	3					789
5	4
6	5
7
8	123
9	1
10	2
11	3
12	4
13	5
14
15	789
16	1
17	2
18	3
19	4
20	5
Sheet1

I able to run it now. Thanks!!!!!
Let say the Acc in sheet 2? how should i amend the script?

 

[Zot]

When involves another worksheet or even another workbook, it is easier when you create reference. This way you don't have to use Select command.

Here is example

Sub Test()

Dim n As Long
Dim cell As Range, rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set rngData = ws1.Range("A1", ws1.Cells(Rows.Count, "A").End(xlUp))

n = 1
For Each cell In rngData
    If cell = "Value" Then
        n = n + 1
        cell = ws2.Range("F" & n)
    End If
Next

End Sub