Was hoping someone could help me with a little piece of vba code? My raw data looks like this:
希望有人可以用一小段vba代码帮助我吗?我的原始数据如下所示:
1 A B C D E F G H I J K L M N
2 1 311000063552511.00 9/07/2013
3 2 13552500 135525
4 3 871 535316-613 30/03/2015 12:39:20 0 CRD PURCHASE 22900 0 22900 EFTPOS
5 3 872 407220-029 30/03/2015 15:28:39 0 CRD PURCHASE 22900 0 22900 EFTPOS
6 3 873 456472-840 30/03/2015 16:41:04 0 CRD PURCHASE 22900 0 22900 EFTPOS
7 2 13553400 135534
8 3 2077 494053-070 30/03/2015 8:56:31 0 CRD PURCHASE 37763 0 37763 EFTPOS
9 3 2078 456443-621 30/03/2015 9:55:40 0 CRD PURCHASE 17647 0 17647 EFTPOS
10 3 2079 455701-331 30/03/2015 11:35:44 1 CHQ PURCHASE 40546 0 0 EFTPOS
11 3 2080 455701-331 30/03/2015 11:36:27 55 CRD PURCHASE 40546 0 0 EFTPOS
12 3 2086 552350-690 30/03/2015 14:21:51 0 CRD PURCHASE 22166 0 22166 EFTPOS
13 3 2087 455702-239 30/03/2015 15:24:34 0 CHQ PURCHASE 23647 0 23647 EFTPOS
14 3 2088 455701-685 30/03/2015 16:21:31 0 CRD PURCHASE 22166 0 22166 EFTPOS
15 2 13555200 135552
16 3 1544 516361-514 30/03/2015 11:55:06 0 CHQ PURCHASE 22900 0 22900 EFTPOS
17 3 1545 407220-745 30/03/2015 14:00:36 0 CRD PURCHASE 24153 0 24153 EFTPOS
What I hope to do is: Col A determines what I want to do. So a "2" in Col A means I want to copy the number in Col B1, B5 etc. And replace all the "3's" in Col A with this number.
我希望做的是:Col A决定我想做什么。因此,Col A中的“2”表示我想复制Col B1,B5等中的数字。并用该数字替换Col A中的所有“3”。
I want the sheet to look like this once finished:
一旦完成,我希望表单看起来像这样:
1 1 A B C D E F G H I J K L M N
2 1 311000063546919 28/03/2015
3 13552500 871 535316-613 30/03/15 0.527314815 0 CRD PURCHASE 22900 0 22900 EFTPOS
4 13552500 872 407220-029 30/03/15 0.644895833 0 CRD PURCHASE 22900 0 22900 EFTPOS
5 13552500 873 456472-840 30/03/15 0.695185185 0 CRD PURCHASE 22900 0 22900 EFTPOS
6 13553400 2077 494053-070 30/03/15 0.372581019 0 CRD PURCHASE 37763 0 37763 EFTPOS
7 13553400 2078 456443-621 30/03/15 0.413657407 0 CRD PURCHASE 17647 0 17647 EFTPOS
8 13553400 2079 455701-331 30/03/15 0.483148148 1 CHQ PURCHASE 40546 0 0 EFTPOS
9 13553400 2080 455701-331 30/03/15 0.483645833 55 CRD PURCHASE 40546 0 0 EFTPOS
10 13553400 2086 552350-690 30/03/15 0.598506944 0 CRD PURCHASE 22166 0 22166 EFTPOS
11 13553400 2087 455702-239 30/03/15 15:24:34 0 CHQ PURCHASE 23647 0 23647 EFTPOS
12 13553400 2088 455701-685 30/03/15 16:21:31 0 CRD PURCHASE 22166 0 22166 EFTPOS
13 13555200 1544 516361-514 30/03/15 11:55:06 0 CHQ PURCHASE 22900 0 22900 EFTPOS
14 13555200 1545 407220-745 30/03/15 14:00:36 0 CRD PURCHASE 24153 0 24153 EFTPOS
15 13555300 2730 450949-935 30/03/15 0.369259259 0 CRD PURCHASE 22900 0 22900 EFTPOS
16 13555300 2731 456463-730 30/03/15 0.553101852 0 CRD PURCHASE 34966 0 34966 EFTPOS
17 13555300 2732 552033-957 30/03/15 0.628530093 0 CRD PURCHASE 27147 0 27147 EFTPOS
Hope this doesn't prove too difficult for the great minds on here! Code I'm currently using isn't right!
希望这对于这里的伟大思想来说并不太难!我目前使用的代码不对!
Dim CellValue As String
Dim RowCrnt As Integer
Dim RowMax As Integer
With Sheets("Sheet2") ' Replace Sheet1 by the name of your sheet
RowMax = .Cells(Rows.Count, "B").End(xlUp).Row
For RowCrnt = 1 To RowMax
CellValue = .Cells(RowCrnt, 1).Value
If Left(CellValue, 2) = "1355" And CellValue = "3" Then
.Cells(RowCrnt, 1).Value = CellValue
End If
Next
End With
Any help is greatly appreciated!
任何帮助是极大的赞赏!
Thanks! :)
1 个解决方案
#1
I hope I understood what you wanted to do correctly,
我希望我明白你想要做的事,
This code replaces every 3 in A with the first value of B where A=2
此代码将A中的每3个替换为B的第一个值,其中A = 2
Dim CellValue As String
Dim RowCrnt As Integer
Dim RowMax As Integer
With Sheets("Sheet2") ' Replace Sheet1 by the name of your sheet
RowMax = .Cells(Rows.Count, "B").End(xlUp).Row
For RowCrnt = 1 To RowMax
If .Cells(RowCrnt,1) = "2" Then
.Range("A:A").Replace What:="3", Replacement:=.Cells(RowCrnt,2), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next
End With
#1
I hope I understood what you wanted to do correctly,
我希望我明白你想要做的事,
This code replaces every 3 in A with the first value of B where A=2
此代码将A中的每3个替换为B的第一个值,其中A = 2
Dim CellValue As String
Dim RowCrnt As Integer
Dim RowMax As Integer
With Sheets("Sheet2") ' Replace Sheet1 by the name of your sheet
RowMax = .Cells(Rows.Count, "B").End(xlUp).Row
For RowCrnt = 1 To RowMax
If .Cells(RowCrnt,1) = "2" Then
.Range("A:A").Replace What:="3", Replacement:=.Cells(RowCrnt,2), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next
End With