मैं किसी अन्य तालिका में मानों का उपयोग करके पंक्ति द्वारा तीन आसन्न स्तंभों में मानों को प्रतिस्थापित करना चाहता हूं। अब तक मैंने इस उत्तर से कोड को संशोधित किया है: https://www.mrexcel.com/forum/excel-questions/745557-visual-basic-applications-find-replace-multiple-values-multiple-cells- Print.html

मैं चाहता हूं कि कॉलम ए, बी, और सी के तहत कॉलम जी, एच और आई में खोजा जाए और कॉलम डी, ई और एफ के तहत सूचीबद्ध मानों द्वारा प्रतिस्थापित किया जाए (अधिक मान हैं इसलिए कोड को कॉलम से लिंक करने की आवश्यकता है , केवल वह विशेष श्रेणी नहीं)। मैं चाहता हूं कि यह कोड केवल अनुक्रम को बदल दे यदि कॉलम ए, बी और सी में सभी तीन मान जी, एच और आई के सभी तीन मानों से मेल खाते हैं।

पहले दो कॉलम, जी और एच के लिए कोड प्रकार काम करता है (सुनिश्चित नहीं है कि यह कॉलम I पर क्यों नहीं होगा), लेकिन अगर मेरे पास कॉलम ए में समान मान हैं लेकिन कॉलम बी में अलग-अलग मान हैं तो यह पहचान नहीं कर रहा है इसे तीन आसन्न कोशिकाओं को एक साथ खोजने की जरूरत है, यह कॉलम ए में क्या है और कॉलम डी के मूल्य के साथ कॉलम जी में मान को प्रतिस्थापित करेगा लेकिन यह उसके बाद रुक जाता है और कुछ भी नहीं बदलता है।

मेरा कोड:

Sub test()

Dim NumV, AlphaV As String
Dim irow As Long

irow = Cells(1000000, 1).End(xlUp).Row

Do Until irow = 0
    TskOwn = Cells(irow, 1)
    NewTskOwn = Cells(irow, 4)
    TskSup = Cells(irow, 2)
    NewTskSup = Cells(irow, 5)
    TskIni = Cells(irow, 3)
    NewTaskIni = Cells(irow, 6)

If Cells(irow, 7) = TskOwn And Cells(irow, 8) = TskSup And Cells(irow, 9) = TskIni Then
    Application.ReplaceFormat.Interior.ColorIndex = (34)
    Columns(7).Replace What:=TskOwn, Replacement:=NewTskOwn, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False
    Application.ReplaceFormat.Interior.ColorIndex = (22)
    Columns(8).Replace What:=TskSup, Replacement:=NewTskSup, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False
    Application.ReplaceFormat.Interior.ColorIndex = (12)
    Columns(9).Replace What:=TskIni, Replacement:=NewTskIni, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False
End If

irow = irow - 1
Loop


End Sub

जो मेरे पास है:

+--------+--------+---------+-------+-------+-------+--------+--------+---------+
| A      | B      | C       | D     | E     | F     | G      | H      | I       |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
| Ty     | Sheri  | Richard | Name1 | Name2 | Name3 | Ty     | Sheri  | Richard |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
| Rachel | Sheila | Sheila  | Name4 | Name5 | Name2 | Rachel | Sheila | Sheila  |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
| Rachel | Bob    | Paul    | Name6 | Name7 | Name1 | Ty     | Sheri  | Richard |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Sarah  | Kyle   | Robert  |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Rachel | Bob    | Paul    |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Ty     | Sheri  | Richard |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Rachel | Sheila | Sheila  |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Rachel | Bob    | Paul    |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Ty     | Sheri  | Richard |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Ty     | Sheri  | Richard |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Rachel | Sheila | Sheila  |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Ty     | Sheri  | Richard |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Sarah  | Kyle   | Robert  |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+
|        |        |         |       |       |       | Rachel | Sheila | Sheila  |
+--------+--------+---------+-------+-------+-------+--------+--------+---------+

जो मैं चाहता हूं:

+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| A      | B      | C       | D     | E     | F     | G     | H     | I      |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| Ty     | Sheri  | Richard | Name1 | Name2 | Name3 | Name1 | Name2 | Name3  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| Rachel | Sheila | Sheila  | Name4 | Name5 | Name2 | Name4 | Name5 | Name2  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| Rachel | Bob    | Paul    | Name6 | Name7 | Name1 | Name1 | Name2 | Name3  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Sarah | Kyle  | Robert |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name6 | Name7 | Name1  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 | Name3  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Name5 | Name2  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name6 | Name7 | Name1  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 | Name3  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 | Name3  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Name5 | Name2  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 | Name3  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Sarah | Kyle  | Robert |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Name5 | Name2  |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+

मुझे क्या मिल रहा है:

+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| A      | B      | C       | D     | E     | F     | G     | H     | I      |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| Ty     | Sheri  | Richard | Name1 | Name2 | Name3 | Name1 | Name2 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| Rachel | Sheila | Sheila  | Name4 | Name5 | Name2 | Name4 | Name5 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
| Rachel | Bob    | Paul    | Name6 | Name7 | Name1 | Name1 | Name2 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Sarah | Kyle  | Robert |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Bob   | Paul   |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Name5 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Bob   | Paul   |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Name5 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name1 | Name2 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Sarah | Kyle  | Robert |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+
|        |        |         |       |       |       | Name4 | Name5 |        |
+--------+--------+---------+-------+-------+-------+-------+-------+--------+

किसी भी सहायता की सराहना की जाएगी! मुझे यकीन नहीं है कि मैं क्या गलत कर रहा हूँ।

धन्यवाद!!

0
Sarah 5 अक्टूबर 2018, 20:16

1 उत्तर

सबसे बढ़िया उत्तर

इस कोड को आजमाएं:

Sub replaceNames()

For i = 1 To ActiveSheet.Range("I1").End(xlDown).Row
    For j = 1 To ActiveSheet.Range("A1").End(xlDown).Row
        If Cells(i, 7) = Cells(j, 1) And Cells(i, 8) = Cells(j, 2) And Cells(i, 9) = Cells(j, 3) Then
            Cells(i, 7) = Cells(j, 4)
            Cells(i, 8) = Cells(j, 5)
            Cells(i, 9) = Cells(j, 6)
        End If
    Next j
Next i

End Sub
0
Daniel Souza 5 अक्टूबर 2018, 23:28