0

I am trying to check values in column A on sheet A if it is a yes then values in column B and C need to be copied to column A and B in sheet B. This needs to be looped until it has checked all of the values and checked all of the rows. The code executes but it only copies the values onto the same sheet and only does it for the first line of values. I have tried selecting and activating but to no avail. Code is below:

Dim Count As Integer        'Count from workbook open
Dim CountVal As Integer
Dim Check As String        'check value for yes or no
Dim ufCheckvalue As Integer      'Gets go/nogo from userform
Dim Row1 As Integer         'row number for add/remove sheet
Dim Row2 As Integer         'row number for summary sheet
Dim SecNum As Integer       'value for section number
Dim Descrip As String   'value for description

ListCount = Range("B" & Rows.Count).End(xlUp).Row
Count = ListCount         'initalize values
CountVal = 0
Check = ""
ufCheckvalue = 0
Row1 = 10       'first row of values on add/remove sheet
Row2 = 5        'first row of values on summary sheet
SecNum = 0
Descrip = ""

ufContinue.Show       'user has to confirm it wants to run program
If ufCheckvalue = 1 Then
Unload ufContinue
    Do
    Check = Range(Cells(1, Row1)).Value                 'gets the check value
        If Check = "Yes" Then                           'checks to see if yes
            SecNum = Range(Cells(2, Row1)).Value        'sets values if yes
            Descrip = Range(Cells(3, Row1)).Value
            Sheets("Summary of Estimate").Select        'selects estimate page
            Selection.Activate                          'activates estimate page
            Range(Cells(1, Row2)).Select                'selects cell to insert value into
                Selection.Value = SecNum                'inserts value
            Range(Cells(2, Row2)).Select                'selects cell to insert value into
                Selection.Value = Descrip               'inserts value
            Sheets("Add-Remove").Select                 'goes back to original worksheet
            Row2 = Row2 + 1                             'adds one to row2 so it will index to next line
        End If
        Row1 = Row1 + 1                                 'changes rows for check value
        CountVal = CountVal + 1                         'adds one to count value
    Loop Until CountVal = Count                         'loops until it has looped countval number of times

ElseIf ufCheck = 0 Then                                 'makes the update button visible
Unload ufContinue
cmdUpdate.Visible = True    'Shows the update button
End If

I do apologize if that does not really make sense. I learned enough to be dangerous in college.

Community
  • 1
  • 1
user2970715
  • 1
  • 2
    read this: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Dmitry Pavliv May 14 '14 at 16:24

1 Answers1

2
Set ws = Worksheets("Summary of Estimate")
Set ws2 = Worksheets("Add-Remove")
Dim i As Integer

For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
    If ws.Range("a" & i).Value = "Yes" Then
    ws.Range("b" & i).Value = ws2.Range("a" & i).Value
    ws.Range("c" & i).Value = ws2.Range("b" & i).Value
    End If
Next i
Arie
  • 96
  • 4
  • Finally got back into the code on this one. I tried your response but it did not seem to be getting any of the values. My guess for this would be that there are blank cells in the "A" column, how would I modify this to run over the blank spaces? – user2970715 Feb 23 '15 at 01:06