Anyone good at visual basic / excel / macros?

S

Shalomp

Banned
Joined
Feb 20, 2012
Messages
49,802
Reaction score
13,245
super new to vba so was hoping if i can get some help here.

I have a table of numbers with column names as shown in the screenshot below. The problem is the columns are not always in the same places. I want to create a macro that first searches for the existence of the column named "Close". Once it is found, I want to select the empty cell in row 2 to the right of the entire table (which is cell H2 in this case). Then I want to key in a simple formula which takes the adjacent cell below the header "Close" and subtract the next adjacent cell below it (which is E2 - E3 in this case). Finally I want to drag down the formula to all the empty cells below it until it reaches the last row of the table that has data in it. Another problem is after running the macro, i want the formula to appear in the function bar of excel when i click on any of the cells.



Ahw59Mf.png




can anyone please help? been trying to do this the past 2 days! :(
 
P

peterchan75

Supremacy Member
Joined
Apr 26, 2003
Messages
6,722
Reaction score
529
I assume row2(E2) - row3(E3) and the result goes into row2. If the next Close is blank, then no calculation is done. Your data must be in "Sheet1". Otherwise, change "Sheet1" to any name that you are using.

Here is the Excel VBA code.
Code: 
Sub Delta_close()

Dim i As Integer, j As Integer, k As Integer

    'Find the next empty column to the right.
    i = Sheets("Sheet1").UsedRange.Columns.Count
    
    'Name this column as "Delta Close"
    Sheets("Sheet1").Range("A1").Offset(0, i) = "Delta Close"
    
    'Look for Close
    For j = 0 To i - 1
        If Sheets("Sheet1").Range("A1").Offset(0, j) = "Close" Then
            Exit For
        End If
    Next j
    
    'Start from row number 2 to the last row and stop at an empty row
    k = 1
    While Sheets("Sheet1").Range("A1").Offset(k, 0) <> ""
        'Check if the next Close is not blank
        If Sheets("Sheet1").Range("A1").Offset(k + 1, j) <> "" Then
            Sheets("Sheet1").Range("A1").Offset(k, i) = Sheets("Sheet1").Range("A1").Offset(k, j) - Sheets("Sheet1").Range("A1").Offset(k + 1, j)
        End If
        k = k + 1
    Wend

End Sub
 
S

Shalomp

Banned
Joined
Feb 20, 2012
Messages
49,802
Reaction score
13,245
peterchan75 said:
I assume row2(E2) - row3(E3) and the result goes into row2. If the next Close is blank, then no calculation is done. Your data must be in "Sheet1". Otherwise, change "Sheet1" to any name that you are using.

Here is the Excel VBA code.
Code: 
Sub Delta_close()

Dim i As Integer, j As Integer, k As Integer

    'Find the next empty column to the right.
    i = Sheets("Sheet1").UsedRange.Columns.Count
    
    'Name this column as "Delta Close"
    Sheets("Sheet1").Range("A1").Offset(0, i) = "Delta Close"
    
    'Look for Close
    For j = 0 To i - 1
        If Sheets("Sheet1").Range("A1").Offset(0, j) = "Close" Then
            Exit For
        End If
    Next j
    
    'Start from row number 2 to the last row and stop at an empty row
    k = 1
    While Sheets("Sheet1").Range("A1").Offset(k, 0) <> ""
        'Check if the next Close is not blank
        If Sheets("Sheet1").Range("A1").Offset(k + 1, j) <> "" Then
            Sheets("Sheet1").Range("A1").Offset(k, i) = Sheets("Sheet1").Range("A1").Offset(k, j) - Sheets("Sheet1").Range("A1").Offset(k + 1, j)
        End If
        k = k + 1
    Wend

End Sub
Click to expand...




bro thank u so much! very kind of u to help me out :)

hmmm only the number appears in the function bar? is there a way to have the formula appear i.e =E2-E3? :)
 
Last edited:
P

peterchan75

Supremacy Member
Joined
Apr 26, 2003
Messages
6,722
Reaction score
529
Code: 
Sub Delta_close()

Dim i As Integer, j As Integer, k As Integer
Dim col As String

    'Find the next empty column to the right.
    i = Sheets("Sheet1").UsedRange.Columns.Count
    
    'Name this column as "Delta Close"
    Sheets("Sheet1").Range("A1").Offset(0, i) = "Delta Close"
    
    'Look for Close
    For j = 0 To i - 1
        If Sheets("Sheet1").Range("A1").Offset(0, j) = "Close" Then
            Exit For
        End If
    Next j
    
    'Convert column number to alphabet
    col = ConvertToLetter(j + 1)
    
    'Start from row number 2 to the last row and stop at an empty row
    k = 1
    While Sheets("Sheet1").Range("A1").Offset(k, 0) <> ""
        'Check if the next Close is not blank
        If Sheets("Sheet1").Range("A1").Offset(k + 1, j) <> "" Then
            'Sheets("Sheet1").Range("A1").Offset(k, i) = Sheets("Sheet1").Range("A1").Offset(k, j) - Sheets("Sheet1").Range("A1").Offset(k + 1, j)
            Sheets("Sheet1").Range("A1").Offset(k, i) = "=" & col & (k + 1) & "-" & col & (k + 2)
        End If
        k = k + 1
    Wend

End Sub

'This function converts column number to alphabet

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function
 
S

Shalomp

Banned
Joined
Feb 20, 2012
Messages
49,802
Reaction score
13,245
peterchan75 said:
Code: 
Sub Delta_close()

Dim i As Integer, j As Integer, k As Integer
Dim col As String

    'Find the next empty column to the right.
    i = Sheets("Sheet1").UsedRange.Columns.Count
    
    'Name this column as "Delta Close"
    Sheets("Sheet1").Range("A1").Offset(0, i) = "Delta Close"
    
    'Look for Close
    For j = 0 To i - 1
        If Sheets("Sheet1").Range("A1").Offset(0, j) = "Close" Then
            Exit For
        End If
    Next j
    
    'Convert column number to alphabet
    col = ConvertToLetter(j + 1)
    
    'Start from row number 2 to the last row and stop at an empty row
    k = 1
    While Sheets("Sheet1").Range("A1").Offset(k, 0) <> ""
        'Check if the next Close is not blank
        If Sheets("Sheet1").Range("A1").Offset(k + 1, j) <> "" Then
            'Sheets("Sheet1").Range("A1").Offset(k, i) = Sheets("Sheet1").Range("A1").Offset(k, j) - Sheets("Sheet1").Range("A1").Offset(k + 1, j)
            Sheets("Sheet1").Range("A1").Offset(k, i) = "=" & col & (k + 1) & "-" & col & (k + 2)
        End If
        k = k + 1
    Wend

End Sub

'This function converts column number to alphabet

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function
Click to expand...




eureka! :eek::o

thanks again bro! i am scrutinising the coding so i can learn. as they say.... give a man a fish and he eats for the day. teach a man to fish and he eats for a lifetime. appreciate that u took the extra effort to put in those comments in green fonts to explain the coding so that i can understand faster

its not easy and its taking very long but im gonna gut it out :(
 
Last edited:
S

Shalomp

Banned
Joined
Feb 20, 2012
Messages
49,802
Reaction score
13,245
To all those reading, i hope anyone who has such problems can post their queries here. Also hope that those who are good at this can be kind and generous like peterchan75 and lend a helping hand. This way those who are interested to learn or improve their skills in VBA/excel/macros can learn from one another :o

~happy TGIF~

peace out! :);)
 
P

peterchan75

Supremacy Member
Joined
Apr 26, 2003
Messages
6,722
Reaction score
529
@TS,
I simply gave you a solution to your problem. But it doesn't mean that it's the best solution. Very seldom that we script formula for spreadsheet. We usually calculate the solution in the script. Putting too many formula in the spreadsheet will slow it down since Excel needs to calculate every time when there is an update.

Judging for your sample data, I am guessing you are doing some processing of stock data. Using applications such as Workstation, Metastock, Ninja Trader, Amibroker are better alternative to Excel. It's very much efficient in processing stock quote compare to Excel.
 
S

Shalomp

Banned
Joined
Feb 20, 2012
Messages
49,802
Reaction score
13,245
peterchan75 said:
@TS,
I simply gave you a solution to your problem. But it doesn't mean that it's the best solution. Very seldom that we script formula for spreadsheet. We usually calculate the solution in the script. Putting too many formula in the spreadsheet will slow it down since Excel needs to calculate every time when there is an update.

Judging for your sample data, I am guessing you are doing some processing of stock data. Using applications such as Workstation, Metastock, Ninja Trader, Amibroker are better alternative to Excel. It's very much efficient in processing stock quote compare to Excel.
Click to expand...



Hi! yes i am aware of that. and thank you for your suggestions! :)
 
You must log in or register to reply here.
Important Forum Advisory Note
This forum is moderated by volunteer moderators who will react only to members' feedback on posts. Moderators are not employees or representatives of HWZ Forums. Forum members and moderators are responsible for their own posts. Please refer to our Community Guidelines and Standards and Terms and Conditions for more information.
Top