none
VB in excel RRS feed

  • Question

  • Why is the code not working?

    I am using excel's macro

    Sub test()

    'Dim B As Integer
    'Dim D As Integer

    to have information in  column B and I want to compare it to column D.  both column B and D are lists  with names in them. B has 659 and E has 18458. some of the name in B are in D. now D and another Column E that have information that goes with D.  so

    D5 has the name and E5 has information about the name in D5

    and I want to copy the  that information in to Column C

    but it will not copy. it run and the color changes and the " why" prints out , but the information in the Column E will not printout in Column C

    here is the code:

    A = 5
    Range("C" & A).Value = "WHY?"
    'Range("A3").Interior.Color = vbBlue
    For B = 2 To 659
    For D = 2 To 18458
    If Range("B" & B).Value = Range("D" & D).Value Then
    Range("C" & B).Value = Range("E" & D).Value ' also I have tried .text and . Value2
    Range("C" & B).Interior.Color = vbRed
    End If

    Next D
    Next B
    End Sub

     

    • Moved by Mike Feng Tuesday, September 18, 2012 11:48 AM VBA (From:Visual Basic General)
    Thursday, September 13, 2012 9:35 PM

Answers

  • Hi riten,

    Thanks for posting in the MSDN Forum.

    please try following snippet, it is create via your description. I hope it can help you.

    Sub test()
        Dim xlWorkbook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim i As Integer
        Dim j As Integer
        Dim Bv As Range
        Dim Dv As Range
        
        i = 2
        
        Set xlWorkbook = Application.ActiveWorkbook
        Set xlWorksheet = xlWorkbook.ActiveSheet
        
        While xlWorksheet.Range("B" & i).Value <> ""
            Set Bv = xlWorksheet.Range("B" & i)
            j = 2
            While xlWorksheet.Range("D" & j).Value <> ""
                Set Dv = xlWorksheet.Range("D" & j)
                If Bv.Value = Dv.Value Then
                    xlWorksheet.Range("C" & i).Value = xlWorksheet.Range("E" & j).Value
                End If
                j = j + 1
                t1 = t1 + 1
            Wend
            i = i + 1
            t = t + 1
        Wend
        
        Set xlWorksheet = Nothing
        Set xlWorkbook = Nothing
    End Sub

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, September 19, 2012 6:35 AM
    Moderator

All replies

  • You know that this forum is for VB in visual Studio and not for VB in Excel?

    Success
    Cor

    Thursday, September 13, 2012 10:09 PM
  • oh is there one for VB in Excel?
    Thursday, September 13, 2012 11:30 PM
  • Dear riten,

    follow this url for your VBA forum 

    Visual Basic for Applications (VBA)

    http://social.msdn.microsoft.com/forums/en/isvvba/threads

    or select category & forum like below -

    thanks.


    Pl. Mark/Proposed as Answer if found your solution Vote as Helpful if related to your topic. Always Motivate others by performing this Action.

    Friday, September 14, 2012 3:08 AM
  • Hi riten,

    Thanks for posting in the MSDN Forum.

    please try following snippet, it is create via your description. I hope it can help you.

    Sub test()
        Dim xlWorkbook As Excel.Workbook
        Dim xlWorksheet As Excel.Worksheet
        Dim i As Integer
        Dim j As Integer
        Dim Bv As Range
        Dim Dv As Range
        
        i = 2
        
        Set xlWorkbook = Application.ActiveWorkbook
        Set xlWorksheet = xlWorkbook.ActiveSheet
        
        While xlWorksheet.Range("B" & i).Value <> ""
            Set Bv = xlWorksheet.Range("B" & i)
            j = 2
            While xlWorksheet.Range("D" & j).Value <> ""
                Set Dv = xlWorksheet.Range("D" & j)
                If Bv.Value = Dv.Value Then
                    xlWorksheet.Range("C" & i).Value = xlWorksheet.Range("E" & j).Value
                End If
                j = j + 1
                t1 = t1 + 1
            Wend
            i = i + 1
            t = t + 1
        Wend
        
        Set xlWorksheet = Nothing
        Set xlWorkbook = Nothing
    End Sub

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, September 19, 2012 6:35 AM
    Moderator