Блог

Мне нужно заполнить значение Sheet 1 Cell A2 с =Sheet 2 Cell A2 помощью, пока связанное значение не станет пустым. Я действительно не знаю, что делать.

Я зашел так далеко, что мне нужны только нули в полях:

 Sub Test1()
    Dim x As Integer
    Dim i As Integer
    Dim wsh As Worksheet
          
    Set wsh = Worksheets("List with Weights")
    Application.ScreenUpdating = False
          
    i = 2
    
    While (wsh.Cells(i, 1)) <> ""
        wsh.Cells(i, 1).FormulaR1C1 = "='Sample Weight'!RC[0]"
        wsh.Cells(i, 2).FormulaR1C1 = "='Sample Weight'!RC[0]"
        wsh.Cells(i, 3).FormulaR1C1 = "='IS Weight'!RC[-1]"
        i = i   1
    Wend
     
    ' Selects cell down 1 row from active cell.
    ActiveCell.Offset(1, 0).Select
    Columns("A:C").Select
    Columns("A:C").EntireColumn.AutoFit
    Application.ScreenUpdating = True
    Range("A1").Select
End Sub
 

Если я вас правильно понял, цикл не нужен. Это то, что вы пытаетесь?

 Option Explicit

Sub Sample()
    Dim wsThis As Worksheet
    Dim wsThat As Worksheet
    Dim wsOther As Worksheet
    
    '~~> Set your relevant worksheets
    Set wsThis = ThisWorkbook.Sheets("List with Weights")
    Set wsThat = ThisWorkbook.Sheets("Sample Weight")
    Set wsOther = ThisWorkbook.Sheets("IS Weight")
    
    '~~> Find the last row in Col A of Sample Weight worksheet
    Dim wsThatLRow As Long
    wsThatLRow = wsThat.Range("A" amp; wsThat.Rows.Count).End(xlUp).Row
    
    '~~> Insert the formula in 1 go in the relevant range
    With wsThis
        .Range("A2:A" amp; wsThatLRow).Formula = "='" amp; wsThat.Name amp; "'!A2"
        .Range("B2:B" amp; wsThatLRow).Formula = "='" amp; wsThat.Name amp; "'!B2"
        .Range("C2:C" amp; wsThatLRow).Formula = "='" amp; wsOther.Name amp; "'!B2"
    End With
End Sub