如何通过VBA将相同的文本传递给列中指定的no.of单元格 [英] How to pass the same text to specified no.of cells in a column through VBA

查看:98
本文介绍了如何通过VBA将相同的文本传递给列中指定的no.of单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述





我的数据如下所示。



输入:


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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆