Basic VBA Copy Paste

[SensualCarrots]

I found several thing out there that get me close, but I don't really know VBA at all, so trying to modify to my needs was, well... rather unsuccessful. I need a rather simple macro.

Basically,

If A25 is empty, copy F24 and paste values in A25, else if A26 is empty, copy F24 and paste values in A26, else if A27 is empty..... and so forth down to A33, and if A33 is not empty, then end and do nothing. F24 is derived from a formula, so it's very important to paste special: paste values.

I also need a macro that will basically clear the form, and wipe out any data in a wide range of cells that I can select. Any help would be appreciated! Thanks!

 

[Joe4]

Here is a macro that will do your first part:

Sub MyMacro1()


    Dim rng1 As Range
    Dim cell As Range
    
    Set rng1 = Range("A25:A33")
    
    For Each cell In rng1
        If cell = "" Then
            cell = Range("F24").Value
            Exit For
        End If
    Next cell
        
End Sub

For your second part, if you just want to clear out whatever cells you have selected, try this:

Sub MyMacro2()
    Selection.ClearContents
End Sub

 

[SensualCarrots]

First part works great, thanks! Second part needs to be a specified group of cells that are all over the place. Clearing all cells may potentially work since everything that I don't want cleared will be locked, but not sure if that will throw an error or not.

 

[Joe4]

This version will clear only the unlocked cells in your selection without any error messages:

Sub MyMacro2()
    
    Dim cell As Range
    
    Application.ScreenUpdating = False
        
    For Each cell In Selection
        If cell.Locked = False Then
            cell.ClearContents
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[SensualCarrots]

genius... Thanks!

 

[Joe4]

You are welcome!

 

[SensualCarrots]

Actually, that only does the ones that are selected as well. I came up with something, seems to be working. Is this the most elegant way?

Sub Button3_Click()Answer = MsgBox("Are you sure you want to clear All User Data? This cannot be undone!", vbYesNoCancel + vbInformation, "Application Message")
If Answer = vbYes Then Else Exit Sub
Sheets("Sheet1").Range("B1:B6, C2:F2, G4:H12, A19, B20, A22, B23, A25:B33, A35:B35, F21:F22").ClearContents




End Sub

 

[Joe4]

Actually, that only does the ones that are selected as well.

That is correct. I did it based on what you said here:

I also need a macro that will basically clear the form, and wipe out any data in a wide range of cells that I can select.

So, you could select any range of cells that you want, and it will only clear the ones that are not protected.

What you did above will work fine. The main difference is that you have to identify exactly which ones to clear ahead of time, instead of just selecting a large range, and letting the code figure it out.
If that doesn't change much, your method would be more efficient, as you are pinpointing the exact cells you want to clear, instead of looping through a bunch of other cells too.
It just initially sounded to me like you wanted something that wasn't so restrictive, where you need to specifically identify the cells to clear.

 

[SensualCarrots]

Ahh yes, forgive me. I meant to ask for a range of cells that I the creator of the spreadsheet can predetermine. I should have worded that better, but I see where you were going now. Thanks again!

 

[Joe4]

No worries!

Glad you got what you need.