PDA

View Full Version : If... Then...



infinity
03-17-2006, 06:56 PM
Hello all you wonderful helpful people at VBAX,:hi:

I have a workbook that I could perform certain actions based on the value of one cell.


If Range("C11") > Range("K1") Then
Range("B11").Select
Selection.Copy
Range("B10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Range("D10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E11").Select
Application.CutCopyMode = False
Selection.Copy
Range("E10").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.unprotect
Range("B11:G11").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B11:F11").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("B10:F10").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("B11").Select
ElseIf Range("C11") <= Range("K1") Then
Range("B10").Select
End If


At that point If Range("C12") > Range("K1) Then I would like to be able to repeat this same thing for the next row down and the next row, until the value in column C, row " ? " is less than or equal to range K1. Once I meet that criteria, then I would like to select the cell in column B of the row that is now empty. I hope this makes sense. You have all been so much help in the past and I hope you can help this time. Thank you so much in advance!!!

Scott

smc2911
03-17-2006, 07:34 PM
Your original code can be simplified quite a bit. Here's a start:

If Range("C11") > Range("K1") Then
Range("B11:E11").Copy
Range("B10").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
ActiveSheet.Unprotect
Range("B11:G11").ClearContents
Range("B11:F11").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("B10:F10").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("B11").Select
End If
As far as the looping you asked for, something like this would do it:

Dim c As Range
Set c = Range("C11")
ActiveSheet.Unprotect

While c > Range("K1")
c.Offset(0, -1).Resize(1, 4).Copy
c.Offset(-1, -1).PasteSpecial Paste:=xlValues
c.Offset(0, -1).Resize(1, 6).ClearContents
With c.Offset(0, -1).Resize(1, 5)
.Locked = False
.FormulaHidden = False
End With
With c.Offset(0, -1).Resize(1, 5)
.Locked = True
.FormulaHidden = True
End With
Set c = c.Offset(1, 0)
Wend
c.Offset(-1, -1).Select
Application.CutCopyMode = False

In pulling this together I noted a there are some inconsistencies in how many cells are copied, cleared and unlocked. Is this intentional?

Sean.

infinity
03-18-2006, 09:44 PM
Hey Sean,

Yes this is intentional, there are formulas that are in some of the cells in these rows that I do not want to be affected by this code. The code you gave me is absolutely fantastic. It did exactly what I was wanting it to do. Thank you!

I am new to VBA, and what usually happens, at least for me, is that once I have one situation resolved, other problems arise as a result. This is the case for this one, although I believe it will be easy to resolved for someone that is proficient with coding, in other words someone other than myself.

The thing that I discovered is that I now need these records that are being copied and pasted to be pasted in decending order based on the date that is in column "C". These records are not necessarily being entered in chronological order and what happens is that as soon as this code finds one record that is <= K1, it stops and may leave some records unaffected by this code that should be included. Do you have any suggestions for this?

The other thing is that the row that is emptied after the code runs needs to have the cells in columns "B:F" unlocked, but only in that row. Thank you so much for being willing to share your expertise, you as well as many others on VBAX have been more helpful than you will ever realize!!!

Thanx,
Scott

smc2911
03-19-2006, 08:00 PM
Scott,

I would suggest adding a Sort: you can add something like this at the start.
Range("B11:I2000").Sort Key1:=Range("C11"), Order1:=xlDescending, Header:=xlNo This will sort the data in columns B to I in rows 11 to 2000 (the latter is an arbitrary choice, but you can make it bigger) in descending order of column C values.

As far as the locking is concerned, there is a slight error in my code. Replacing the last With block with the following should sort it (so to speak).

With c.Offset(-1, -1).Resize(1, 5)
.Locked = True
.FormulaHidden = True
End With

Sean.