Posts by Victor Stefan

Try this item please, because this is one of the ones it doesn't work for me.

Did it work for the 2 items from the example ?

Hello,

I need your assistance for solving an issue with my excel file (I'll attach the file here). I have in Worksheet 3, 2 buttons that represent searchable dropdown lists. They work great, except for the fact that they refuse to show some of the items in the specified cell.

For example if I click on the button and I type "Adaptor", and then select any Adaptor from the list, it will show in cell C24/C25, but if I type "Vana de aerisire" and I select "Vana de aerisire-dezaerisire DN 50 corp fonta cu flansa cu tripla actiune pentru apa curata", it won't show up in C24/C25. This happens for more items. Like: "Baza radier camin beton cu 2 goluri si bancheta Dint.1000, Dext.1240, Hnominal 750 , Htotal 875 mm" etc.

At first I thought that It's because the text is too long, but it's not that. Then I tried to remove the "." and "," from the text and it worked some times, but I still can't figure out why it works only when it wants.

The VBA code for these 2 buttons are in Module 2&3, and also in the Form code (frm_DropDownSearch)

Also The list range is represented by Worksheet Predare, C4:C500.

I have to mention that this is for Excel 2007. Unfortunately, I can't use a newer version.

Do you have any ideas ?

The code for these buttons is from the internet, I am a total noob in vba and I don't know how to modify it. If you have any solution, please post here the modified vba code.

Thank you for your trouble.

Hello Justin,

I've tried your new code. It works great.

As you know, I've sent you in chat the manual version of it that I managed to make, but thank you so much for this answer which is the automated version.

You are the man.

Hello,

I need your assistance with a vba code. I don't know how to change it to do what I need unfortunately.

To make things easier, I will upload my excel file here.

Please check the code below:

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H14:H25")) Is Nothing Then
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim ws3 As Worksheet: Set ws3 = Sheet3
x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0)
If IsNumeric(x) Then
For i = 7 To Columns.Count
If ws2.Cells(1, i).EntireColumn.Hidden = False Then
ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 + CStr(Target.Value)
GoTo MyEnd
End If
Next i
End If
End If
MyEnd:
End Sub

My excel file has at the moment 9 sheets but it will have more sheets similar to the sheets 6 - sheet 9.

What I want is to make the vba code that's added into Sheet 3, to show the result also in Sheet 6-sheet x, (where x is the last sheet I will add), based on 1 condition.

Example:

If WS3: B14 = 13396, and H14 = whatever,

Then show the result of the vba code in WS2, G329 ( 329 being the matching row of the 13396 code inside WS2.)

But also, if I change the B14 with B14 = 12375, and H14 = whatever,

Then show the result of the vba code in WS2, G98 ( 98 being the matching row of the 12375 code inside WS2).

What I said above is what the code does now. I need it to also do this:

If WS3: B14 = 13396, and H14 = whatever, and G9=Sheet6 (It won't actually say sheet 6, but it will be a data validation list)

Then show the result of the vba code in WS2, G329 ( 329 being the matching row of the 13396 code inside WS2.)

And also show the result of the vba code in WS6, F6 (6 being the matching row of the 13396 code inside WS6)

If WS3: B14 = 13396, and H14 = whatever, and G9=Sheet8

Then show the result of the vba code in WS2, G329 ( 329 being the matching row of the 13396 code inside WS2.)

And also show the result of the vba code in WS8, F6 (6 being the matching row of the 13396 code inside WS8)

If G9 = empty, then the vba code should do just the first part where the result is shown only in WS2.

I want to mention that the value of 13396 inside Sheet6 to Sheet x, won't be always at the same row.

1336 can be in Sheet 6 in row 6, but in Sheet 15 can be in row 25. The fact that both Sheet 6 and Sheet 8 has the code 1336 in B6 is a coincidence.

Basically, I want the code to show the results in ws2 everytime, but also to match the row in the other worksheets and based on the condition that G9=whatever, to show the result in the specified sheet too, at the correct row.

I think that it is important to say, I don't need the hidden columns part for the other sheets. Just for WS2 as it is now, but if it's easier to let it there, then let it there.

Any help with this issue will be much appreciated.

Thank you

This is what I need the code to do.

Hello Justin,

This code you wrote here does everything I need it to do. Thank you from the bottom of my heart. You really saved my day. If you didn't do this, I would have had to enter each line manually. Thank you again for this

Quote from Justin Doward

Okay, I think we got there. It makes sense now.

Code

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H14:H25")) Is Nothing Then
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim ws3 As Worksheet: Set ws3 = Sheet3
x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0)
If IsNumeric(x) Then
For i = 7 To Columns.Count
If ws2.Cells(1, i).EntireColumn.Hidden = False Then
ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 & CStr(Target.Value)
GoTo MyEnd
End If
Next i
End If
End If
MyEnd:
End Sub

Display More

If this has solved it, can you mark it as the answer on stack exchange as well. Cheers Justin

Hello Justin, I'm not in front of a computer anymore, but I will try the code tonight when I get home. (4-5 hours from now).

If it's solved I will mark it as solved everywhere I posted it. If it doesn't work still, I will message you. Thank you so much for your work so far.

Hello Justin,

I've put your code in excel and it almost does what I need.

The hidden cells loop is not redundant. This loop works perfectly, please let it in the code.

The only problem is with the location of the answer.

I don't need the answer of the vba code to be shown in the same row as the one it was imputed (the row of ws3 H column).

I need the answer to be like this: IF in ws3 B20 for example is cod AAA, match this code with code AAA from ws2 (which is somewhere between B4:B500) and show the result of the vba code in ws2, column G( with the hidden cells loop ) at the row of ws2 AAA code.

Example:

WS3: B14 = 32849, and H14 = 50,

Then show the result of the vba code in WS2, G4 ( 4 being the matching row of the 32849 code inside WS2.)

But also, if I change the B14 with B14 = 12375, and H14 = 100,

Then show the result of the vba code in WS2, G98 ( 98 being the matching row of the 12375 code inside WS2).

This is what I need the code to do.

Can it be done like that ?

Also, this is a cross-post from superuser. This is the link:

Excel VBA: How can I multiply this code 10 times for 10 different H13,B13 cells? - Super User

Thank you in advance

This is the full excel file. I have vba code in the sheet of Proces verbal de predare. Thank you for your help

Hello,

I will add 2 images of my actual excel file to be able to explain exactly what I need. Please look at images 1 and 2 (which represent 2 sheets in the same excel file):

What I need the code to do is this:

If 'Proces verbal de predare'!B14:B25 matches the value of any of the cells(so not in the same order) from Predare!B4:B500, then whatever value I write inside 'Proces verbal de predare'!H14:H25, do :

Worksheets("Predare").Range("G4").Offset(0, i - 7) = Worksheets("Predare").Range("G4").Offset(0, i - 7).Value + Target.Value

But Instead of G4, I need it to be Gn, with n = the matching row of the value written inside 'Proces verbal de predare'!B14:B25

Here is the full code:

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H14")) Is Nothing And (Range("B14") = "32849") Then
        For i = 7 To Columns.Count
            If Worksheets("Predare").Cells(1, i).EntireColumn.Hidden = False Then
                Worksheets("Predare").Range("G4").Offset(0, i - 7) = Worksheets("Predare").Range("G4").Offset(0, i - 7).Value + Target.Value
                GoTo MyEnd
            End If
        Next i
    End If
    If Not Intersect(Target, Range("H14")) Is Nothing And (Range("B14") = "79504") Then
        For i = 7 To Columns.Count
            If Worksheets("Predare").Cells(1, i).EntireColumn.Hidden = False Then
                 Worksheets("Predare").Range("G5").Offset(0, i - 7) = Worksheets("Predare").Range("G5").Offset(0, i - 7).Value + Target.Value
                GoTo MyEnd
            End If
        Next i
    End If
    If Not Intersect(Target, Range("H14")) Is Nothing And (Range("B14") = "10486") Then
        For i = 7 To Columns.Count
            If Worksheets("Predare").Cells(1, i).EntireColumn.Hidden = False Then
                 Worksheets("Predare").Range("G6").Offset(0, i - 7) = Worksheets("Predare").Range("G6").Offset(0, i - 7).Value + Target.Value
                GoTo MyEnd
            End If
        Next i
    End If
MyEnd:
End Sub

This code has 3 main IF statements for each value of B14 which I introduced manually. I want the B14 to be automatic, instead of me introducing 400 If statements for each one.

If you have any solutions to do this, I will be grateful. Also, if you know the solution, please answer with the modified vba code because I'm a complete noob in coding and I don't know how to adapt it myself. Thank you very much.