Need some help with a macro

[Programs1234]

Hello,

I got stuck on a macro and would love some help.

Please see the attached document below.

What I am trying to do is match information accordingly in order to fill in the correct information for certain cells.

Inside the document, there are three different types of information: List of players, list of markets these players are in, and the percentage they are allowed to lend in.

On the first tab, I have created a table that I have to fill information into and on the second tab, I have raw data that I need to extract information from in order to put into the table of which I have created on the first tab.

My goal for this macro is to auto-fill in the lending percentage for each player for each individual market using and matching the information that is given in the second tab.

I am stuck on how to write a macro that can help automatically use the raw data to fill in the information for me in a proper and accurate manner.

Anyone here have any ideas? Thanks!

[Paul_Hossler]

Maybe a user defined function (UDF)? I'm sure WS functions could do the same, the UDF might not be the most efficient, but IMO it's the the simplest




Option Explicit

Function PlayerPercentage(DataRange As Range, PlayerName As String, CountryName As String) As Variant
Dim i As Long

For i = 2 To Intersect(DataRange, DataRange.Parent.UsedRange).Rows.Count
If DataRange.Parent.Cells(i, 1).Value = PlayerName And DataRange.Parent.Cells(i, 3).Value = CountryName Then
PlayerPercentage = DataRange.Parent.Cells(i, 2).Value
Exit Function
End If
Next i

PlayerPercentage = CVErr(xlErrNA)
End Function

[mdmackillop]

No code required

=SUMPRODUCT(--('Information Sheet'!$A1:$A100=Chart!$A2),--('Information Sheet'!$C1:$C100=Chart!B$1),'Information Sheet'!$B1:$B100)

[Programs1234]

Thank you very much for the responses.

mdmackillop,

I made some additional changes to my spreadsheet. I was wondering if you are still able to utilize the SUMPRODUCT function in this spreadsheet to enter in data from the information tab into the table.

Please note that I have not given every piece of data needed to completely fill up the table. This set up was intentional. I am interested in figuring out if the function might leave something like "#REF" or "#N/A" for spots that the function does not have enough information to fill in for.

Please see the attached.

Thanks.

[mdmackillop]

For your dummy file, two formulae required. Missing/wrong data will not error but will return 0 where the match does not occur.
B3 =SUMPRODUCT(--('Information Sheet'!$A$1:$A$20=Chart!$A3),--('Information Sheet'!$C$1:$C$20=Chart!B$1),--('Information Sheet'!$D$1:$D$20=Chart!B$2),('Information Sheet'!$B$1:$B$20))
C3 =SUMPRODUCT(--('Information Sheet'!$A$1:$A$20=Chart!$A3),--('Information Sheet'!$C$1:$C$20=Chart!B$1),--('Information Sheet'!$D$1:$D$20=Chart!C$2),('Information Sheet'!$B$1:$B$20))

[Programs1234]

mcmackillop,

Thank you very much for the help. I tried using the two formulas and it gives accurate information for some cells and inaccurate information for others. I was wondering if you would not mind letting me know how to properly apply the two formulas to the entire spreadsheet. Thank you very much!

[mdmackillop]

See attached.
Paste formulae into B3 and C3.
Select and drag both cells down
Select and drag both columns across

[Programs1234]

Hi mdmackillop,

Thank you very much for the response and information.

I think I am making some errors when it comes to putting in the information. This is what I get when I put in your formulas. For the most part, it is very similar but there are some slight errors on my spreadsheet. Please see the attached.

I just insert in the two formulas. I drag down the B3 cell and thats all i do with it because when I drag it across, for some reason, all of the cell values become inaccurate.

I drag down the C3 cell and I drag it across and it gets me what the file that I have attached.

The cells that are highlighted in purple are the cells that contain inaccurate information. Please let me know what I am doing wrong.

Thank you very much!2072420724

[greyangel]

If you want a macro solution here you go. I have already tested it and it works out. Please tell me if this is what you are looking for. If you would like to go through the macro and see what it does step by step go into the module and hit "F8"


Sub runthis()
'Macro created by Robbie DePalma
Application.ScreenUpdating = False
Sheets("Chart").Select
x = 3
y = 1
Z = 2
player = "not empty"
Do Until player = ""
player = Cells(x, y).Value
y = y + 1
country = "not empty"
Do Until Cells(Z, y).Value = ""
precentvariable = ""
country = Cells(Z, y).End(xlUp).Value

If country = "" Then
country = Cells(Z, y).End(xlUp).Offset(, -1).Value
End If

Status = Cells(Z, y).Value


With Sheets("Information Sheet")
On Error Resume Next
.ShowAllData
On Error GoTo 0

.Range("$A$1").AutoFilter field:=1, Criteria1:="" & player & ""
.Range("$C$1").AutoFilter field:=3, Criteria1:="" & country & ""
.Range("$D$1").AutoFilter field:=4, Criteria1:="" & Status & ""
precentvariable = .Range("B1").End(xlDown).Value
.ShowAllData
End With
If precentvariable = "" Then
Cells(x, y).Value = "No limit set"
Else
Cells(x, y).Value = precentvariable
End If

y = y + 1

Loop
x = x + 1
y = 1

Loop

Range("B3").End(xlDown).EntireRow.Clear

MsgBox "Chart updated"

Application.ScreenUpdating = True

End Sub

[Programs1234]

Thank you very much for the macro. The macro looks great and it certainly does help. However, like what mdmackillop said, I do not think there is a need for code if you can just use the SUMPRODUCT function to get the information of which you want. I am more interested in learning how to apply the two SUMPRODUCT function correctly as it is the most simple and basic way to do it.

Thanks again!