Update Code for specific entity deposits

Sandler

Board Regular
Joined
Sep 15, 2011
Messages
162
All deposits get posted to one account, which we then have to transfer amounts to the entity it belongs to.

*

In column I , I have a deposit from one of my entities in Columns J:CT.* To get to the entity that the deposit belongs to I do an XLtoRight and grab whatever is in Row 2 above that item.* The Row that shows the entity.* Only one deposit makes up the number seen in column I.

*

The rows I am getting information from are from filtered information.* i.e. I can have row 743, followed by 794, followed by 861.

At this time my code works if I open up column A, set it up to show the row number, and then reference the cell in my function.

Is there a way to get this function to work by having Excel know what row it is in, without having to show row numbers in column A?

*

See code I have so far below, thanks in advance:

*

Const col_deposit = 8 'column H

Const row_start = 2 'row where you have the string Deposit and Entity1,...

*

Function getEntityCol(row As Integer) As Integer

*** Dim start_col As Integer

*** Dim col As Integer

*** Dim result As Integer

*** Dim cur_entity As Integer

***

****result = 0

***

****start_col = col_deposit + 2

***

****end_col = Cells(row_start, start_col).End(xlToRight).Column

***

****For col = start_col To end_col

***

********cur_entity = Val(Cells(row, col).Value)

*******

********If cur_entity > 0 Then

*********** result = col

*********** Exit For

***

********End If

***

****Next col

*** getEntityCol = result

End Function

*

Function getEntity(row As Integer) As String

*** getEntity = Cells(row_start, getEntityCol(row)).Value

End Function
 

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top