VBA to take input from user and process data

[swapnilk]

Hi,

I am using the undernoted formula:

C2=COUNTIF(A:A,A2)=COUNTIF(B:B,B2)

The issue is that the range A:A and B:B can change and the result will also be displayed in different cell. So is there a way to prompt user to enter three variables i.e. columns where A:A data is, column for B:B data and where the user wants to display the result.

For e.g. on running the code it will ask user to select three variables i.e. location of C2, A:A and B:B, based on the input entered by the user the script will replace the formula "C2=COUNTIF(A:A,A2)=COUNTIF(B:B,B2)" with the user entered input and display the result.

Any help is appreciated!

 

[mumps]

Try:

Sub Processdata()
    Application.ScreenUpdating = False
    Dim loc As String, col1 As String, col2 As String
    loc = InputBox("Enter the location, for example C2.")
    col1 = InputBox("Enter the first column letter.")
    col2 = InputBox("Enter the second column letter.")
    Range(loc).Formula = "=COUNTIF(" & col1 & ":" & col1 & "," & col1 & "2)=COUNTIF(" & col2 & ":" & col2 & "," & col2 & "2)"
    Application.ScreenUpdating = True
End Sub

 

[swapnilk]

Hi, your solution worked perfectly. I have different formulas and was trying to use the same code that you have given but excel is adding @ symbol in front of the formula and the formula is not functioning the way it should. E.g for undernoted formula

=TRANSPOSE(FILTER(A:A,B2=B:B))

I am using this code

Sub Test()
    Application.ScreenUpdating = False
    Dim loc As String, col1 As String, col2 As String
    loc = InputBox("Enter the Result location e.g , for example C2.")
    col1 = InputBox("Enter 1st Column Letter e.g A")
    col2 = InputBox("Enter 2nd Column Letter e.g. B")
    Range(loc).Formula = "=TRANSPOSE(FILTER(" & col1 & ":" & col1 & "," & col2 & 2 & "=" & col2 & ":" & col2 & "))"
    Application.ScreenUpdating = True
End Sub

The formula that gets pasted in C2 by running the above code is

=@TRANSPOSE(FILTER(A:A,B2=B:B))

Any idea why the @ symbol is coming after = sign. The formula also doesn't give the required result.

 

[swapnilk]

Hi, your solution worked perfectly. I have different formulas and was trying to use the same code that you have given but excel is adding @ symbol in front of the formula and the formula is not functioning the way it should. E.g for undernoted formula

=TRANSPOSE(FILTER(A:A,B2=B:B))

I am using this code

Sub Test()
    Application.ScreenUpdating = False
    Dim loc As String, col1 As String, col2 As String
    loc = InputBox("Enter the Result location e.g , for example C2.")
    col1 = InputBox("Enter 1st Column Letter e.g A")
    col2 = InputBox("Enter 2nd Column Letter e.g. B")
    Range(loc).Formula = "=TRANSPOSE(FILTER(" & col1 & ":" & col1 & "," & col2 & 2 & "=" & col2 & ":" & col2 & "))"
    Application.ScreenUpdating = True
End Sub

The formula that gets pasted in C2 by running the above code is

=@TRANSPOSE(FILTER(A:A,B2=B:B))

Any idea why the @ symbol is coming after = sign. The formula also doesn't give the required result.

ok, so i solved the problem by adding Formula2 in place of Formula

 

[mumps]

Glad it worked out.