VBA Pathfinder

[Dogga786]

Im tryng to use sumifs formula but its not working because both the files are on Dropbox on computer and when enter the formula from my computer its using the full path "C:\User\username\dropbox\foldername" and when someone else opens the file the links dont work because their path is different "c:\users\username2\dropbox\foldername "

Is there any way you can come around this problem ? and have the formula work no matter which computer is used to open the file (both the files are on dropbox)

I was thinking maybe I can have vba code to read current user's credentials and use that in the path "C:\("current user")\dropbox\foldername\filename"

I am really new to VBAs but Im finding it very interesting

Thank you

 

[Max1616]

Hello, two steps to solve this issue:

1. Add this code to a module:

Function UserName()
UserName = (Environ$("Username"))
End Function

2. Replace any instances in a formula that contains the file path with this:
CONCATENATE("c:\users",UserName(),"\dropbox\foldername")


For example:

='C:\username1\dropbox\foldername\[test.xlsx]Sheet1'!$A$1
changes to
=CONCATENATE("'C:",UserName(),"\dropbox\foldername\[test.xlsx]Sheet1'!$A$1")

 

[Max1616]

For some reason the forum is deleting the "slash" after "C:" in my example. Remember to include the "slash"

 

[Dogga786]

Thank you for your help but its still not working

this is the formula i used in a cell

=SUMIFS('C:\Users\kelly\Dropbox (Essence Properties)\Projects\Job 1010 - Towne\Sales and Marketing\Sales Analysis and Pricing\Towne Pricing.xlsx'!netprice,'C:\Users\kelly\Dropbox (Essence Properties)\Projects\Job 1010 - Towne\Sales and Marketing\Sales Analysis and Pricing\Towne Pricing.xlsx'!date,">="&H6,'C:\Users\kelly\Dropbox (Essence Properties)\Projects\Job 1010 - Towne\Sales and Marketing\Sales Analysis and Pricing\Towne Pricing.xlsx'!date,"<="&EOMONTH(H6,0))

this is what i put in the VBA
Range("B41:M41").Formula = _
"=SUMIFS('Towne Pricing.xlsx'!netprice,'Towne Pricing.xlsx'!date,"">=""&R[-35]C,'Towne Pricing.xlsx'!date,""<=""&EOMONTH(R[-35]C,0))"

VBA thing works if the other workbook is open on the side then it updates the numbers

how can I tell VBA to use the function sumifs and for the file path use C:\ users\(use current users name)\ dropbox\ etc.

 

[Max1616]

I think I better understand what you are trying to do. Try this:

Sub Formula()

Dim filepath As String

filepath = "C:\Users\" & Environ$("Username") & "\Dropbox (Essence Properties)\Projects\Job 1010 - Towne\Sales and Marketing\Sales Analysis and Pricing\Towne Pricing.xlsx"

Range("B41") = "=SUMIFS('" & filepath & "'!netprice,'" & filepath & "'!date,"">="" &H6,'" & filepath & "'!date,""<=""&EOMONTH(H6,0))"
Range("B41").Copy
Range("B41:M41").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False

End Sub