Excel macro help - If statement with a variable range

Question

I am creating a macro to help organize a data dump (sheet 1) into an invoice (sheet 2). I have coded most of the macro, but am stuck on the following.

I want the macro to read column Y on sheet 1, which is a variable range (can be 2 rows to 50) and check if it says "CB". If this is true, then E11 on sheet 2 is Yes, otherwise No, and so on until it reaches the end of column Y on sheet 1.

I have the following:

Sheets("Data_Dump").Select
intCounter = 1
While Range("Y" & (intCounter + 1)) <> ""
    intCounter = intCounter + 1
Wend
intCardSize = intCounter
MsgBox (intCardSize)

Sheets("Data_Dump").Select

If Range("Y" & intCardSize) = "CB" Then
    Sheets("Reconciliation").Select
    Range("E11:E" & intCardSize).Select
    Range("E11") = "Yes"
End If

The while range seems to work and it displays the number of cells with text in column Y, but I can't seem to wrap my head around how to get it to move from Y1 to Y2 and so on and then paste the response into E11 then E12 and so on.

Move the "Wend" statement from where it is to after "End If" — OpiesDad, May 11 '16 at 18:04
Also, see this: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — OpiesDad, May 11 '16 at 18:05
Also change "Range("Y" & intCardSize)" to "Range("Y" & intCounter)" — OpiesDad, May 11 '16 at 18:06

score 0 · Answer 1 · answered May 11 '16 at 19:47

0

I hope I understood your code goal as follows

With Sheets("Data_Dump")
    With Sheets("Reconciliation").Range("E11").Resize(.Cells(.Rows.Count,1).Row)
        .Formula="=IF('Data_Dump'!Y1="CB", "Yes","")"
        .Value= .Value
    End With
End With

answered May 11 '16 at 19:47

user3598756

28,893
4
18
28

The OP had a misunderstanding about how to code VBA. This answer does not explain the issue they were having, and, further, uses even more difficult to understand VBA without explaining it. The OP will not learn much from pasting your code into their workbook. To improve this, comment on the problem they were having and how to fix it, then, potentially, include your additional solution, along with an explanation. – OpiesDad May 11 '16 at 20:18
@OpiesDad You know, I learned much more studying different solutions then those I expected. Nonetheless I never downvoted who thought differently – user3598756 May 11 '16 at 20:25
I have no problem with offering a different solution. This solution doesn't explain anything. It is fair to say that I went too far in suggesting that your solution needed to include a solution in the method asked for and for that, I apologize. The code should at least be explained, however, even as much as stating why this method is preferable to the one they were attempting. – OpiesDad May 11 '16 at 20:33

OpiesDad · Accepted Answer · 2016-05-12T15:57:46.513

The problem that you are having is that your code doesn't loop to try to compare. The While loop that you have only looks to see if there is something in the next cell. In fact, it actually skips the first row, but maybe that was intentional.

Dim dataSheet As WorkSheet
Dim recSheet As Worksheet
Dim lngCounter As Long 'Use long because an integer may not be big enough for large dataset.
Dim intCardSize As Long

Set dataSheet = ThisWorkbook.Sheets("Data_Dump")
Set recSheet = ThisWorkbook.Sheets("Reconciliation")
'You want to set the sheets to a variable instead of referring to the whole path each time
'Also, Note the usage of "ThisWorkbook" which guarantees the worksheet 
'is coming from the one with code in it.
lngCounter = 2 'If you want to start looking at row 2, start at row 2 with
               'the variable instead of starting the variable and checking var+1
While dataSheet.Range("Y" & (lngCounter)) <> ""
     'While there is a value in the column

     'intCardSize = intCounter 'Not sure what this is supposed to do
     'MsgBox (intCardSize) 'This looks like debugging.  Commenting out.


     If dataSheet.Range("Y" & lngCounter) = "CB" Then
          'Check each row as you go through the loop.
          'Sheets("Reconciliation").Select 
          'Avoid selecting sheet/range.  Unneccessary work for computer.

          recSheet.Range("E" & (9 + lngCounter)) = "Yes"
          'Set reconciliation sheet value to "Yes" if data sheet has "CB"
          'The reconciliation sheet starts on row 11, whereas the datasheet
          'starts at row 2 ,a difference of 9 
     Else
          recSheet.Range("E" & (9 + lngCounter)) = "No"
          'Otherwise set to no.
     End If
     lngCounter = lngCounter + 1
Wend
intCardSize = lngCounter - 1 'It's been increased to one past the last item.
MsgBox intCardSize 'Display the last row checked.

Hey thanks so much for this! Having the Wend at the beginning did nothing, as your comment up top stated. I made some changes and I can't seem to get it work properly...I want it to start pasting into column E11 (on recSheet) and go down from there. So if there are 15 rows in Y1, on dataSheet, recSheet would paste from E11 to E26, yes or no. I changed"E" to "E1", which starts to paste at E11 but only goes 9 iterations before jumping to row 110. Do you know why this might be? Something with the the counter? — Kamal, May 12 '16 at 15:24
I misread the question....the code as provided will paste starting in row 1 on the recSheet. By changing "E" to "E1", you were initially pasting in cell "E11", but then, once lngCounter was equal to '10', you began pasting in cell, "E110" as "E1" was concatenated with '10'. One way to do it is, "E" & (9+lngCounter). I'll update the answer to reflect this. The 9 is described in the comment in the code. — OpiesDad, May 12 '16 at 15:28

Excel macro help - If statement with a variable range

2 Answers2