Excel VBA For each + if statement exception issue

Question

I'm trying to implement a simple for-each statement that is supposed to copy data from one worksheet (template) to another (raw data), followed by a another 'clean-up' for-each + If statement, which is supposed to clear all the cells previously copied except for 5 cells which have pre-defined calculations.

I began with for-each statement which cleared all the fields (and worked OK), except for the fact that later I figured that I need exceptions on some of the fields. So, I added a simple If statement which instructs the sub to go to the next cell without clearing it in case of those few particular cell ranges.

For some reason when running that code, it clears all the fields from within the defined range except for field K15 which isn't included in the exception, and I have no idea why that happens ;__; I tried troubleshooting that but can't come up with anything.

My code is pasted below. Any advice will be appreciated - including any tips to improve the code. Thanks!

Sub CopyData()

Dim TargetRow, CopyRange, Cell As Range
Dim RowCount, ColumnCount, ColumnLast, ColC As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

ColumnCount = 1
ColumnLast = 51
ColC = 1
Set ws = ThisWorkbook.ActiveSheet
Set CopyRange = Range("C10:C15,C17:C18,E12,E17:E18,K4:K6,K9,K11:K45")

Sheets("RawData").Activate
RowCount = Range("A1").CurrentRegion.Rows.Count + 1

Set TargetRow = Range(Cells(RowCount, ColumnCount), Cells(RowCount, ColumnLast))

For Each Cell In CopyRange
    TargetRow.Cells(ColC).Value = Cell.Value
    ColC = ColC + 1
Next Cell

ws.Activate

For Each Cell In CopyRange
    If Cell = Range("K4") Or Cell = Range("K5") Or Cell = Range("K6") Or Cell = Range("K9") _
    Or Cell = Range("K46") Then
        GoTo Forward
    Else
        Cell.Value = ""
    End If
Forward:
Next Cell

Application.ScreenUpdating = True

End Sub

Do you realise that Cell = Range("K4") (etc) is shorthand for Cell.Value = Range("K4").Value? That If statement is comparing the value of each cell to the values of various other cells, it is not comparing the addresses. (I couldn't understand the question - too early in the morning and I haven't had my morning coffee yet - but, if that is the issue, use Cell.Address = "$K$4" etc.) Or maybe have two ranges - one for copying and one for cleaning up (which excludes the ones you don't want to clean up). — YowE3K
– YowE3K, Commented Jul 14, 2017 at 19:40
I did not realize that! That's it, works like a charm, sweet thanks!!! if you have any other suggestions what can be improved here I'd more more than grateful :-) — WAR
– WAR, Commented Jul 14, 2017 at 19:44
do not use reserved words, or what look like reserved words, as variable names .... Cell looks like Cells, which is reserved .... use Cel or myCell or rCell — jsotola
– jsotola, Commented Jul 14, 2017 at 21:39

YowE3K · Accepted Answer · 2017-07-14 19:49:03Z

Your line saying

If Cell = Range("K4") Or Cell = Range("K5") Or _
   Cell = Range("K6") Or Cell = Range("K9") Or _
   Cell = Range("K46") Then
    GoTo Forward

is equivalent to

If Cell.Value = Range("K4").Value Or Cell.Value = Range("K5").Value Or _
   Cell.Value = Range("K6").Value Or Cell.Value = Range("K9").Value Or _
   Cell.Value = Range("K46").Value Then
    GoTo Forward

I think you meant to use

If Cell.Address = Range("K4").Address Or Cell.Address = Range("K5").Address Or _
   Cell.Address = Range("K6").Address Or Cell.Address = Range("K9").Address Or _
   Cell.Address = Range("K46").Address Then
    GoTo Forward

which is equivalent to

If Cell.Address = "$K$4" Or Cell.Address = "$K$5" Or _
   Cell.Address = "$K$6" Or Cell.Address = "$K$9" Or _
   Cell.Address = "$K$46" Then
    GoTo Forward

But you might like to also consider having one Range variable which includes the cells to be copied, and another Range variable which includes the cells to be cleaned up.

jsotola · Accepted Answer · 2017-07-14 22:11:25Z

0

this should give you an idea how to simplify some of your code

single step this code using F8 and look at your worksheet at the same time

sub test()

    ' the select statements are only to show the range, and are not needed

    Range("a1").Select                                                ' get highlight out of the way
    Range("C10:C15,C17:C18,E12,E17:E18,K4:K6,K9,K11:K45") = "abc123"
    Range("C10:C12,C17:C18,E12").ClearContents
    Range("C10:C15,C17:C18,E12,E17:E18,K4:K6,K9,K11:K45").SpecialCells(xlCellTypeBlanks).Select
    Range("C10:C15,C17:C18,E12,E17:E18,K4:K6,K9,K11:K45").Select   
    Range("a1").Select
    Range("C10:C15,C17:C18,E12,E17:E18,K4:K6,K9,K11:K45").ClearContents
end sub

edited Jul 14, 2017 at 22:11

answered Jul 14, 2017 at 21:47

jsotola

2,2691 gold badge15 silver badges29 bronze badges

1 Comment

WAR Over a year ago

ClearContents won't work, as there are some merged cells there. Other than that, not sure how I would be supposed to use above suggestions? Either way - I changed the IF statement into a separate range to clean up. That should be faster on the code ;-) Thank you all for help!

Collectives™ on Stack Overflow

Excel VBA For each + if statement exception issue

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related