如何通过VBA将相同的文本传递给列中指定的no.of单元格 [英] How to pass the same text to specified no.of cells in a column through VBA
问题描述
我的数据如下所示。
输入:
Loc_id名称
0 hyd
HYD001 hyd
0 hyd
0 hyd
abc.a ban
0 ban
0 ban
Ram pune
0 xyz
0 ban
Loc_id名称
0 hyd
HYD002 hyd
0 hyd
0 hyd
abc.a ban
0 ban
0 ban
Ram pune
0 xyz
0 ban
我需要查看以下数据格式
必填项:
loc_id名称
HYD001 hyd
HYD001 hyd
HYD001 hyd
HYD001 hyd
HYD001禁令
HYD001禁令
HYD001禁令
HYD001 pune
HYD001 xyz
HYD001禁令
HYD002 hyd
HYD002 hyd
HYD002 hyd
HYD002 hyd
HYD002禁令
HYD002禁令
HYD002禁令
HYD002 pune
HYD002 xyz
HYD002禁令
先谢谢。
我的尝试:
我尝试使用下面的代码。但这段代码是静态工作的。但我的要求是动态更改数据
Sub REPLACE()
工作表(Sheet1)。列(A)。REPLACE _
什么:=Abc.a,替换:= HYD001,_
SearchOrder:= xlByColumns,MatchCase:= True
End Sub
检查: http ://www.codeproject.com/Questions/1126932/How-to-delete-sub-header-columns-in-excel-VBA#answer2 [ ^ ]
基于以上原则,您可以根据需要更改代码:
Sub Cleaning3()
Dim wsh 作为工作表
Dim i As 整数 ' ,idcounter As Integer
' context
设置 wsh = ThisWorkbook.Worksheets(< span class =code-digit> 1 )
'
' idcounter = 1
' 起始行
i = 2
' 直到col中的单元格。 A不为空
wsh.Range( A& i)<>
' < span class =code-comment> if zero - 从单元格下方获取值
如果 wsh.Range( A& i)= 0 然后
wsh.Range( A& ; i)= wsh.Range( A& i).Offset(RowOffset:= 1 )
结束 如果
' 如果单元格包含非数字值而且它不是id - 从单元格上方获取值
如果 LCase(wsh.Range( A & i))<> loc_id 和 不 wsh.Range( A& i) 喜欢 UCase(wsh.Range( B2))& * 然后
wsh.Range( A& i)= wsh.Range( A& i).Offset(RowOffset:= - 1)
结束 如果
' ifid - 删除整行
如果 LCase(wsh.Range( A& i))= loc_id 然后
wsh.Range( A& i).EntireRow.Delete xlShiftUp
i = i - 1
结束 如果
i = i + 1
循环
结束 子跨度>
Hi,
I have my data as below.
input:
Loc_id name
0 hyd
HYD001 hyd
0 hyd
0 hyd
Abc.a ban
0 ban
0 ban
Ram pune
0 xyz
0 ban
Loc_id name
0 hyd
HYD002 hyd
0 hyd
0 hyd
Abc.a ban
0 ban
0 ban
Ram pune
0 xyz
0 ban
I need to see data as below format
Required Op:
loc_id name
HYD001 hyd
HYD001 hyd
HYD001 hyd
HYD001 hyd
HYD001 ban
HYD001 ban
HYD001 ban
HYD001 pune
HYD001 xyz
HYD001 ban
HYD002 hyd
HYD002 hyd
HYD002 hyd
HYD002 hyd
HYD002 ban
HYD002 ban
HYD002 ban
HYD002 pune
HYD002 xyz
HYD002 ban
Thanks in Advance.
What I have tried:
I have tried with below code.But this code is working statically.But my requirement is to change data Dynamically
Sub REPLACE()
Worksheets("Sheet1").Columns("A").REPLACE _
What:="Abc.a", Replacement:="HYD001", _
SearchOrder:=xlByColumns, MatchCase:=True
End Sub
Check this: http://www.codeproject.com/Questions/1126932/How-to-delete-sub-header-columns-in-excel-VBA#answer2[^]
Based on above you were able to change the code to your needs:
Sub Cleaning3() Dim wsh As Worksheet Dim i As Integer ', idcounter As Integer 'context Set wsh = ThisWorkbook.Worksheets(1) ' 'idcounter = 1 'starting row i = 2 'till cell in col. A is not empty Do While wsh.Range("A" & i) <> "" 'if zero - get value from below cell If wsh.Range("A" & i) = 0 Then wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=1) End If 'if cell contains non-numeric value and it's not a "id" - get value from above cell If LCase(wsh.Range("A" & i)) <> "loc_id" And Not wsh.Range("A" & i) Like UCase(wsh.Range("B2")) & "*" Then wsh.Range("A" & i) = wsh.Range("A" & i).Offset(RowOffset:=-1) End If 'if "id" - remove entire row If LCase(wsh.Range("A" & i)) = "loc_id" Then wsh.Range("A" & i).EntireRow.Delete xlShiftUp i = i - 1 End If i = i + 1 Loop End Sub
这篇关于如何通过VBA将相同的文本传递给列中指定的no.of单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!