问将动态数据存储在Excel字段中

EN

Sub BuildAWSGroups()    
Dim AWSPrefix As Long, FindName As Long, NextFree As Long, LastFree As Long, EndRow As Long, runningtotal As Long    
Dim UserName As String    
Dim awsrow As Long, outputrow As Long    
Dim wsi, wso As Worksheet    

' name the sheets    
Set wsi = Sheets("aws_input")    
Set wso = Sheets("aws_output")    


' length of the AWS prefix -> aws iam list-groups-for-user --user-name    
AWSPrefix = 41    

'set values to all the variables    
FindName = 0    
NextFree = 0    
LastFree = 0    
EndRow = 0    
runningtotal = 0    
awsrow = 1    
outputrow = 2    

'Find the last non-blank cell in column A(1)    
EndRow = wsi.Cells(Rows.Count, 1).End(xlUp).Row    

' do stuff so long as you don't go past the last row of data    
Do While runningtotal <= EndRow    

' the last test for NextFree throws an error, so we'll look for the error    
On Error Resume Next    
' get the next non-blank row number    
NextFree = wsi.Range("a" & awsrow & ":a" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row    
' is there was an error then we need to calculate the next non-blank cell differently
If NextFree = LastFree Then
    NextFree = Cells.Find(What:="*", _
    After:=Range("a" & (LastFree + 2)), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
NextFree = awsrow + NextFree

End If

' find location of the user name in the aws data row
wsi.Activate
FindName = InStr((AWSPrefix + 1), wsi.Cells(awsrow, 1).Value, " ")
'extract the user name
UserName = Trim(Mid(wsi.Cells(awsrow, 1).Value, (AWSPrefix + 1), (FindName - AWSPrefix)))
'copy user name to output
wso.Cells(outputrow, 1).Value = UserName

' copy the aws groups for this user
wsi.Activate
wsi.Range(Cells((awsrow + 1), 1), Cells((NextFree - 1), 1)).Copy
'paste and transpose the groups to the user row on the output sheet
wso.Activate
wso.Cells(outputrow, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

' update variables for the loop
LastFree = NextFree
awsrow = LastFree + 2
outputrow = outputrow + 1
runningtotal = awsrow

Loop
End Sub