VBA Check for multiple cell values and copy the cell in column A

Goldyyyy

New Member
Joined
Nov 9, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
Hello!

So I have this code that checks for the values in column "L" in this case it's 25. But I want to change this code so that it actually checks for the value in column "J" and "K". So for example right now if we get the value 25, it copy's the cell in column A and paste's it in different sheet in cell "G1".

What I want to accomplish is to check for the values in the column "J" and "K" and if for example both of the values are "5" then again copy the cell value in Column "A" and paste it the other sheet in "G1" because when you multiple 5*5 it's 25, but if the values are for example "J" is 5 and "K" is 4 then it's 20, so it should paste the cell value from column "A" into the other sheet in cell "F1". But again if the value in "J" is 4 and in "K" it's 5 then also 20, but now it should actually paste the value now not in "F1"but "G2" a It's like a multiplier and according to the values of these both numbers I have to put them in the correct place in the map that you can see below. I guess it will be a lot of if else statements or something, because it has to check all the possible values which are in column "J" 1-5 and in "K" also 1-5.

THIS IS THE CODE THAT I HAVE RIGHT NOW.

VBA Code:
Public Sub FindingValues()
    Dim val As Integer, result As String, firstAddress As String
    Dim a As Range

    val = 25
    Set a = Sheets("riskuregistrs").Range("L:L").Find(val, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)

    If Not a Is Nothing Then
        firstAddress = a.Address

        Do
            If Len(result) > 0 Then
                result = result & "," & a.Offset(0, -11).Text
            Else
                result = a.Offset(0, -11).Text
            End If

            Set a = Cells.FindNext(a)
        Loop While Not a Is Nothing And a.Address <> firstAddress
    End If

    Sheets("Riskukarte").Range("G1").Value = result
End Sub

These are the excel sheet's where I have to take the value from and the third image is the map where the values from column "A" from the first sheet should be pasted. As you can see in cell "G1" it pasted all the cell values that had number 25 in column "L".

enter image description here

enter image description here

enter image description here

I will appreciate any help I could get. Thank you so much in advance! If there is any other questions regarding this, please - ask.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,915
I think you should start recording your own macros, you will discover a new world; start from here: https://support.microsoft.com/en-us...recorder-974ef220-f716-4e01-b015-3ea70e64937b

In that way you will discover that the command to remove the border of a selected shape is Selection.ShapeRange.Line.Visible = msoFalse
And you can add this line to my Sub Marker, for example in this position:
VBA Code:
    Selection.ShapeRange.ZOrder msoBringToFront
    Selection.ShapeRange.Line.Visible = msoFalse  '<<< No border
End Sub

99% of the code you see in my Sub Marker was generated by the macro recorder.
Ok, the macro recorder will not create for example the code for my Sub AddMk, but it will be of great help for the trivial but time consuming coding

Bye
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Goldyyyy

New Member
Joined
Nov 9, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
Hey Anthony! Everything now looks perfect! I can't describe how happy I am for your help and I appreciate everything you did for me. This will help big time! Thank you so, so much! I wish there were more people like you! 💯
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,878
Members
410,643
Latest member
sng
Top