Pre-populating

[greentom22]

Hi

I'm trying to figure out the easiest way to pre-populate cells based on a selection in a central cell above.

When a user selects a certain number in a drop-down box in a cell, I would like that selection to be automatically inserted into the new cells as a default.

A further wrinkle would be that these defaults would be inserted only if a cell in that row has a number entered.

So: If A1 = 24% (selected from drop down box)
Then C3 = 24% (automatically selected from drop down box but can be changed)
Only if A3 = 1 and not empty (ie user has selected to use this row.)
If A3 is empty, then B3 is empty.

So far I've done the whole sheet without using VBA & macros (I'm a amateur at VBA) but I have a feeling I'll need to use VBA to get this done. So please, as simple as possible would be great.

Thanks
GreenTom

 

[Joe4]

Yep.

Check out "Offset Property (Range Object)" in Excel VBA help for formal details and examples.

 

[greentom22]

error messege

now I'm getting an error messege. As you can imagine, there are other parts to this model.

After a user puts a number into column B and the pre-populated number calls into column L. Works great. But then the user will enter a number into column 3 which adds to the lines formula (lets say its B*C/(1-L))

Lets say the user makes a mistake and decides he had to change his row. He highlights B & C and hits delete. At this point an error messege comes up:

"Run time error '13': Type mismatch".

Its weird bc if the user deletes cells individually, no problem. Just when he would highlight and hit delete, it causes a problem. Does it have to do with the Target.Offset thing?

 

[Joe4]

No, it has nothing to do with the OFFSET. These kind of event procedures often have trouble when you try to do something on a multiple cell range.

Since this is happening when we are simply trying to delete data, and we don't need the macro to run at that point, we can accomodate this with a slight modification to the code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    
'   Set range to trigger macro if a value is entered into this range
    Set myRange = Range("B25:B32, B38:B45, B51:B58")
    
'   Check to see if cell just changed is in specified range
    If (Not Intersect(Target, myRange) Is Nothing) And (Target.Count = 1) Then
'   If there are values in N6 and specified range, copy N6 to column L of same row
        If Target <> 0 And Range("N6") <> 0 Then
            Target.Offset(0, 10) = Range("N6")
        End If
    End If

End Sub

 

[greentom22]

thank you so much. you were a huge huge help.

 

[Joe4]

Your welcome. Glad I was able to help.