problems with replicating VBA code (every other line)

**redbroyer** · 03-16-2011, 07:21 AM

ok, so i'm working on a balance sheet type of spreadhseet.

i have figured out how to get the results that i want by using a macro, yet am failing at having it replicated on every other (odd) row.

i had thought about just repeating the macro by changing the row number in each instance as there's only 15 lines that i would need to do this for, but that's not working either AND i also thought there would be an easier way to accomplish this.

i am open to any suggestions that you all might have. essentially, the code allows the cell to perform a basic math function unless an alpha designator is placed in a different cell, which in turn, clears its contents and allows the user to manually input the amount.

the code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b9:c9")) Is Nothing Then
Exit Sub
Else
If Range("B9").Value = "in" Then
Range("E9").ClearContents
Range("C9").ClearContents
Else

Range("E9") = Range("c9").Value * 0.5

End If

If Range("B9").Value = "sa" Then
Range("F9").ClearContents
Range("C9").ClearContents
Else
Range("F9").ClearContents
Range("f9") = Range("c9").Value * 0.05

End If

If Range("B9").Value = "pc" Then
Range("G9").ClearContents
Range("C9").ClearContents
Else

Range("G9") = Range("c9").Value * 0.05

End If

If Range("B9").Value = "bi" Then
Range("H9").ClearContents
Range("C9").ClearContents
Else

Range("H9") = Range("c9").Value * 0.4

End If
End If
End Sub

**pike** · 03-16-2011, 10:36 PM

Hi redbroyer
Welcome to the forum.
It hard to know exactly but maybe..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xrow As Long
If Intersect(Target, Range("b9:c24")) Is Nothing And Application.WorksheetFunction.Even(Target.Row) Then
Exit Sub
Else
xrow = Target.Row
Select Case Target.Value
   Case "in"
     Range("E" & xrow) = Range("c" & xrow).Value * 0.5
  Case "sa"
  
  Case "pc"
  
  Case "bi"
  
  
 End Select
End If

End Sub

**redbroyer** · 03-17-2011, 06:43 PM

thank you for the welcome.

i tried what you suggested and it still did not work.

this is what i'm working on.

in the first row, i have a balance carry over from the previous sheet. the second row is an example of the "in" statement which means that the cell under the inventory column is cleared out so that the user and manually input a number. (in this case it's an expense)

the third row is an example of how it should look if the "in", "sa", "pc", or "bi" are not put into the reason column. when these do not show up in the reason column, i want the spreadsheet to look to the cell in the c column and perform the mathematical operations upon that total. (50%, 5%, 5%, and 40%)

i need the reason to also be able to accept text input that describes the transaction if it is not "in", "sa", "pc", or "bi". (such as sold a stereo to Bill)

the formula that i have works but i cannot get it to replicate in the other yellow shaded rows. obviously the white rows are balance trackers while the yellow shaded rows would be the transaction rows. if i could get my current formula to be applied to rows 9, 11, 13, 15, 17, etc i would be thrilled. i have been unable to do that as of yet though.

i'm hoping that was a better explanation. i'm sorry that i didn't include that to begin with. i have been staring at this thing for so long that i'm boggled. lol

thank you again for the attempt and i appreciate you looking at this.

i have attached a screen shot so that you can get a better idea of what it looks like.

**pike** · 03-18-2011, 01:06 AM

looks good but not helpful in excel forum - upload a workbook

**redbroyer** · 03-18-2011, 03:28 PM

Originally Posted by pike

looks good but not helpful in excel forum - upload a workbook

ok, here's the workbook. it's actually in 97-2003 format.

**pike** · 03-18-2011, 05:06 PM

Hi redbroyer

I think you enter data in columns b or c?
explination is unclear but try

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Intersect(Target, Range("b9:C38")) Is Nothing Then
        Exit Sub
    Else
        If Application.WorksheetFunction.Odd(Target.Row) Then
            Select Case Range(Target.Address).Value
            Case "in"
                If Target.Column = 2 Then
                    Range("E" & Target.Row).ClearContents
                    Range("C" & Target.Row).ClearContents
                Else
                    Range("E" & Target.Row) = Range("c" & Target.Row).Value * 0.5
                End If
            Case "sa"
                If Target.Column = 2 Then
                    Range("F" & Target.Row).ClearContents
                    Range("C" & Target.Row).ClearContents
                Else
                    Range("F" & Target.Row).ClearContents
                    Range("f" & Target.Row) = Range("c" & Target.Row).Value * 0.05
                End If
            Case "pc"
                If Target.Column = 2 Then
                    Range("G" & Target.Row).ClearContents
                    Range("C" & Target.Row).ClearContents
                Else
                    Range("G" & Target.Row) = Range("c" & Target.Row).Value * 0.05
                End If
            Case "bi"
                If Target.Column = 2 Then
                    Range("H" & Target.Row).ClearContents
                    Range("C" & Target.Row).ClearContents
                Else
                    Range("H" & Target.Row) = Range("c" & Target.Row).Value * 0.4
                End If
            End Select
        End If
        End If
        Application.EnableEvents = False
    End Sub

**redbroyer** · 03-19-2011, 01:19 AM

data is being entered in both columns b & c depending on the type of transaction.

for instance. if the data is for a sale, then in column "b" the item sold might be input and in column "c" the amount of the sale would be input.

however, if in column "b" there is "in", "sa", "bi", or "pc", then that means that there is a business expense and there would be nothing entered into column "c".

in my example, the first entry (b9) is for an expense towards inventory, or "in". this allows cell e9 to be cleared of its contents and allows the user to manually input the amount of the expense.

in the second entry (b11 & c11) a sale is made for $100 and a description of the merchandise would be entered into b11 while the sale price would be entered into c11. once this is done, columns d, e, f, g, & h all use calculations to determine the amount of each sale that is split into different parts. then the balance is carried over to the next row and keeps a running tally of each column.

i tried the formula, but it seems to not want to work.

**pike** · 03-19-2011, 01:25 AM

It does work just run but i dont think as you want
so you can have entry in both b & c column in the same row?

application.enableevents=true

just in case the events errored and were disabled

**pike** · 03-19-2011, 01:37 AM

i think ive go it
But this only triggers after entry in column c

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Intersect(Target, Range("c9:C38")) Is Nothing Then
        Exit Sub
    Else
        If Application.WorksheetFunction.Odd(Target.Row) Then
              Debug.Print Range(Target.Address).Offset(0, -1).Value
            Select Case Range(Target.Address).Offset(0, -1).Value
         
            Case "in"
               
                    Range("E" & Target.Row) = Range("c" & Target.Row).Value * 0.5
               
            Case "sa"
                
                    Range("f" & Target.Row) = Range("c" & Target.Row).Value * 0.05
               
            Case "pc"
                
                    Range("G" & Target.Row) = Range("c" & Target.Row).Value * 0.05
               
            Case "bi"
                
          
                    Range("H" & Target.Row) = Range("c" & Target.Row).Value * 0.4
              
            End Select
        End If
        End If
        Application.EnableEvents = False
    End Sub

**pike** · 03-19-2011, 01:43 AM

redbroyer
you will need to change the last events to true
but it better to remove both as they are not needed in the example

     End Select
        End If
        End If
        Application.EnableEvents = False
    End Sub

     End Select
        End If
        End If
        Application.EnableEvents = true
    End Sub

**pike** · 03-19-2011, 02:22 AM

or

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B9:C38")) Is Nothing Then
        If Application.WorksheetFunction.Odd(Target.Row) Then
            Debug.Print Range(Target.Address).Offset(0, -1).Value
            Select Case Range("B" & Target.Row).Value
            Case "in"
                Range("E" & Target.Row) = Range("c" & Target.Row).Value * 0.5
                Range("F" & Target.Row).ClearContents
                Range("G" & Target.Row).ClearContents
                Range("H" & Target.Row).ClearContents
            Case "sa"
                Range("F" & Target.Row) = Range("c" & Target.Row).Value * 0.05
                Range("E" & Target.Row).ClearContents
                Range("G" & Target.Row).ClearContents
                Range("H" & Target.Row).ClearContents
            Case "pc"
                Range("G" & Target.Row) = Range("c" & Target.Row).Value * 0.05
                Range("E" & Target.Row).ClearContents
                Range("F" & Target.Row).ClearContents
                Range("H" & Target.Row).ClearContents
            Case "bi"
                Range("H" & Target.Row) = Range("c" & Target.Row).Value * 0.4
                 Range("E" & Target.Row).ClearContents
                 Range("F" & Target.Row).ClearContents
                 Range("G" & Target.Row).ClearContents
            Case Else
                MsgBox "you need a value of 'sa' 'pc' 'bi' 'in' column B row " & Target.Row
            End Select
        End If
    End If
End Sub

**redbroyer** · 03-23-2011, 09:42 AM

i just wanted to let you know everything works perfectly.

i'm having some issues getting another calculation to work, but it's probably just me.

thank you for working on this and providing a solution!

sorry i didn't post this earlier!

**snb** · 03-23-2011, 10:14 AM

or ?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B9:C38")) Is Nothing And target.row mod 2 =1 and instr("insapcbi",cells(target.row,2))>0 Then
    cells(target.row,5).resize(,4).clearcontents
    cells(target.row,4+instr("ispb",left(cells(target.row,2),1))=cells(target.row,3)*choose(instr("ispb",left(cells(target.row,2),1),0.5,0.05,0.05,0.4))
  End If
End Sub

problems with replicating VBA code (every other line)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Re: problems with replicating VBA code (every other line)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions