csv 对于A列中的每个值，我如何在BC列中递增地创建9个URL，并将A列中的值附加到末尾？

我有下面的Excel电子表格

行1是标题行;
列A是任何字符串;和/或
列B是空字符串。

| | A | B |
| --|--| ------------ |
|ID|镜像来源|Image Src|
| AA|||
| ab|||
| 交流|||
| 广告|||
我下面的宏应该获取单元格A2（'aa'）中的值，将其附加到URL（'https：//test.com/aa'），然后附加一个范围从1到9的数字，后跟字符串'. webp'。示例输出将是'https：//test.com/aa-1.webp'。
对于A列中的每个字符串，它应该重复9次。此外，每个重复的值将在B列中伴随一个顺序URL（例如，请参见表）。

问题：

所需输出：*

| | A | B |
| --|--| ------------ |
|ID|镜像来源|Image Src|
| AA| https://test.com/aa-1.webp| https://test.com/aa-1.webp |
| AA| https://test.com/aa-2.webp的| https://test.com/aa-2.webp |
| ......这是什么？|......这是什么？| ... |
| AA| https://test.com/aa-9.webp| https://test.com/aa-9.webp |
| ab|电子邮件地址：http：//| https:// test.com/ab-1.webp|
| ......这是什么？|......这是什么？| ... |

宏生成的内容：*

| | A | B |
| --|--| ------------ |
|ID|镜像来源|Image Src|
| AA|||
| ab| https://test.com/aa-1.webp的| https://test.com/aa-1.webp |
| |https://test.com/aa-2.webp的| https://test.com/aa-2.webp |
| ......这是什么？|......这是什么？| ... |
| |https://test.com/aa-9.webp的| https://test.com/aa-9.webp |
| |https://test.com/ab-1.webp| https://test.com/ab-1.webp |
| ......这是什么？|......这是什么？| ... |
Link to screenshot of spreadsheet showing the problem

宏成功地在列B中顺序创建了9个URL，但它从单元格B3而不是B2开始。
宏不必要地将B2完全留空。
宏保留列A中的ID完全不变。在我的示例中，您可以看到列B中的每个URL都应该与列A中的相应值配对。
到目前为止我所做的尝试：

Sub DuplicateCellsAndGenerateURLs()
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim i As Long
    Dim j As Long
    
    ' Set the range of the source data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' This line will find the last row
    Set sourceRange = Range("A2:A" & lastRow) ' Define the source range
    
    ' Set the destination range in column A and B
    Set destinationRange = Range("A2:B" & (lastRow * 9) + 1) ' Adjust the destination range
    
    ' Duplicate values in column A
    sourceRange.Copy destinationRange.Columns(1)
    
    ' Generate URLs in column B
    i = 2 ' Start index for URL generation
    For Each cell In sourceRange
        If cell.Value = "" Then Exit For ' Stop if an empty cell is encountered in column A
        For j = 0 To 8
            destinationRange.Cells(i + j, "B").Value = "https://test.com" & cell.Value & "-" & j + 1 & ".webp"
        Next j
        i = i + 9 ' Increment index for next URL generation block
    Next cell
End Sub

字符串

**TL;DR：字符串'aa'应该占用A2：A10;'https：//test.com/aa-#.webp'应该占用B2：B10;对列A中的每个值重复（'ab'，'ac'，'ad'等）。

挑战在于，您在想要从sourceRange（列A）中获取信息的同时要覆盖它。sourceRange将在执行循环时更新，因为它是range()。因此，我们丢失了初始数据值。
解决这个问题的一种方法是将sourceRange值临时存储在另一列中。
另一种方法是将值存储在数组中，这样可以防止覆盖“源数据”。如果不显式命令数组进行更新，则不会更新该数组。

Sub DuplicateCellsAndGenerateURLs2()
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim lrow_bc As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim myArr As Variant
    
    ' Set the range of the source data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' This line will find the last row
    myArr = Application.Transpose(Range("A2:A" & lastRow)) ' Define the ID's to be store in an array
    
    j = 1 'set the intial array value (which array number loop should start from)
    For Each myCell In myArr 'Loop through the array
        If IsEmpty(myCell) Then Exit For ' Stop if an empty cell is encountered in the array (from column A)
            lrow_bc = Cells(Rows.Count, "BC").End(xlUp).Row 'Get the last row for the URL destination, update it for every ID
            For k = 1 To 9
                Cells(lrow_bc + k, "BC").Value = "https://exampleimagesource.blob.core.windows.net/inventory/" & myArr(j) & "-" & k & ".webp" 'To get Array number, use myArr(j)
            Next k
            Range("A" & lrow_bc + 1 & ":" & "A" & lrow_bc + 9).Value = myArr(j) 'Populate the ID by nine rows each time.
            j = j + 1 'update array iteration number
    Next myCell

End Sub

字符串

csv 对于A列中的每个值，我如何在BC列中递增地创建9个URL，并将A列中的值附加到末尾？

1条答案

相关问题

热门标签

最新问答