I have some users that are manually entering data from one excel generated from a reporting system and entering in that data into hundreds of cells per day. The format of the report generated excel is static, so I want to map that data to the cells they are entering. The problem is the data is a moving target.

I want the formula (something like ='data'B2) to be entered into the first cell in the range if it is blank. When it is run the next time, if there is data in the first cell in the row, it needs to freeze that data/ remove the formula, and enter the formula in the next empty cell in that row. Any help would be appreciated!

It would look something like this pseudo code wise:

Range = a1-a9
If cell a1 blank then = Data sheet a1
If not blank then freeze cell data/ remove formula and enter formula = Data sheet a1

(and so on if necessary (unless better code))

until all cells in range are full

  • Can you provide a screenshot of an example you are referring to? That would help make it clear for anyone attempting to help you. – Eric F Oct 30 '15 at 15:27

Here's the best I can do with what you've outlined:

Sub test()
Dim src As Worksheet
Set src = Sheets("Sheet3")
Dim dest As Worksheet
Set dest = Sheets("Sheet5")
Dim n As Long
Dim j As Integer
j = dest.Range("A" & Rows.Count).End(xlUp).Row + 1

For i = 1 To j
    If dest.Cells(i, 1) = "" Then
        dest.Cells(i, 1).Formula = "=Sheet3!A1"
        Else:  n = dest.Cells(i, 1)
        dest.Cells(i, 1) = n
    End If
End Sub

If A1 is blank, it will do A1:A2 otherwise it's fine. Change as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.