Please Macro or Formula Help needed

K

K

Situation:-
I have Data in column "G" and "H" (please see belwo)
G H
G68 3980.00
C47 -4000.00
ME4 -200.00
ME4 200.00
PG2 1200.00

I have "VLOOKUP" formula in column "B" that when ever I put
value in any cell of column "A" then it should lookup that value in
columns "G" and "H" and get value from 2nd column or column "H".
Like if I put "G68" in any cell of column "A" then in column "B"
formula
should bring up value next to "G68" which will be "3980.00". Then
in column "D" I have another formula which is "=B1-C1". Which gives
balance when I put any value in coloumn "C". Like if I have value of
"3980.00" in one of column "B" cells and when I put value of
"1000.00"
in next cell of coloumn "C" then I get balance of "2980.00" by
formula
in
column "D".


Macro needed:-
I need a macro that when I get balance in column "D" as above
mentioned "2980.00" and if I put "G68" again in any cell of
column "A" then macro should bring up the balance in column "B"
which was "2980.00". Because of the orginal budget was "3980.00"
and I have taken "1000.00" as mentioned above and now I want macro
to bring balance of budget if I put "G68" again in column "A".
Please note that I mentioned "G68" just to explain the situation
but it can be any value from column "G" (see table above)
I hope you understood what I am trying to say can any one please
help me in this. Thanks
 
N

Nigel

If you replace the value in column B then the lookup formula will be
overwritten - is this what you require?

How about just hiding columns you do not require?
 
M

Mike H

Hi,

I think I understand and don't believe you need a macro, try this instead

A B C D
G68 3980 1000 1980
G68 3980 1000 1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike
 
K

K

Hi,

I think I understand and don't believe you need a macro, try this instead

  A              B                C             D
G68     3980    1000    1980
G68     3980    1000    1980

Column A is a manually input value
Column B formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)
Column C is a manually input number
Column D formula
=VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10))

The formula takes the original value and subtracts any manually input
values. As you will note this only does the first 10 rows but yu can extend
tha ranges to what you want.

Mike







- Show quoted text -
Click to expand...

Thanks for replying Mike. I want 1980 to come in column B when i enter
G68 next time in column A. Actually what i am trying to do is that in
code "G68" i have 3980 budget which come in coloumn B when i enter
"G68" in coloumn A and when i put 1000 or any other figure in column C
then in coloumn D i get balance that i have taken 1000 from 3980
budget but i want a formula that when i enter G68 again in column A
then i want to have same Vlookup formula in column B but this time i
want the balance figure which will be 3980 - 1000 = 2980 to appear in
column B so i know that i got that much budget left to spend
 
M

Mike H

That's what my formula does. Put the 2 formula given in columns B & D and
then drag them down as far as required. For (say) project G68 every time you
put G68 in column A and a value in column C you will get the remaining budget
in column D

Mike
 
K

K

That's what my formula does. Put the 2 formula given in columns B & D and
then drag them down as far as required. For (say) project G68 every time you
put G68 in column A and a value in column C you will get the remaining budget
in column D

Mike






- Show quoted text -
Click to expand...

sorry for being pain Mike but i think may be i am still not be able to
explain to you what i need. is there any way i can send you my
spreadsheet and explain everything on that spreadsheet so you can
understant better what i am trying to say
 
M

Mike H

Something has gone wrong with savefile and I cant post back so here's the
code. Right click the sheet tab, view code and paste this in.
Delete your formulas in G5 - G20
Whenever you put a value in column D your remaining budget is updated in
Column G

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("D5:D20")
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("D5:D20")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = Target.Offset(0, 2).Value
For Each c In MyRange
If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then
budget = budget + c.Value
End If
Next c
Target.Offset(0, 3).Value = budget
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Mike
 
K

K

Hi,

Still no luck with savefile so the file is here

http://www.mediafire.com/?3m9nd0jojgs

Mike






- Show quoted text -
Click to expand...

Thanks Mike thats very nice and this has solved my problem. I have
just two qestions
1 - what line should i add into your macro that if i have no figure in
column D and column F which are "Amount" and "Budget" columns then
column G cell get blank or value should be ="". because at the moment
if i delete every thing i can still see the balance amount.

2 - My second question is that i can see the remaing balance in column
G but what if i want to see this in column F. Because i have deleted
all formulas in column G but if i leave them there and in column F it
lookup the main budget and if i have taken budget then it give me the
balance budget in column F
 
M

Mike H

I'm pleased this answered your problem.

Q1, The code I gave you works by knowing which was the active cell when the
code is called (the target) so if you select multiple cells and delete them
the code doesn't know which is the target because several cells are selected.
So to do what you ask would be a largish task and I woiuldn't have started
from where I did. However, provided you delete cells in column D as a single
cell then this should work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("D5:D20")
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D5:D20")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = Target.Offset(0, 2).Value
For Each c In MyRange
If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then
budget = budget + c.Value
End If
Next c
If Target.Value <> 0 Then
Target.Offset(0, 3).Value = budget
Else
Target.Offset(0, 3).Value = 0
End If
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If
End If

End Sub

I don't understand the second question.

Mike
 
K

K

I'm pleased this answered your problem.

Q1, The code I gave you works by knowing which was the active cell when the
code is called (the target) so if you select multiple cells and delete them
the code doesn't know which is the target because several cells are selected.
So to do what you ask would be a largish task and I woiuldn't have started
from where I did. However, provided you delete cells in column D as a single
cell then this should work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("D5:D20")
   If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("D5:D20")) Is Nothing Then
        If IsNumeric(Target) Then
            On Error Resume Next
                Application.EnableEvents = False
            budget = Target.Offset(0, 2).Value
   For Each c In MyRange
            If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then
                budget = budget + c.Value
            End If
    Next c
           If Target.Value <> 0 Then
                Target.Offset(0, 3).Value = budget
            Else
                Target.Offset(0, 3).Value = 0
            End If
                Application.EnableEvents = True
            'Allow run time errors again
            On Error GoTo 0
        End If
    End If

End Sub

I don't understand the second question.

Mike







- Show quoted text -
Click to expand...

Hi Mike thanks for replying for first question. Please see link below
where i have uploded file which will explain you my second question.
if you can solve my second question then this will completely solve my
whole problem please do reply.
http://www.savefile.com/files/1288928
 
K

K

Hvae a look if this does it for you

http://www.savefile.com/files/1289003

Mike






- Show quoted text -
Click to expand...
Thanks very much Mike you are great that's really works.
I tried adding this into macro (please see below)
If Target.Value <> 0 Then
Target.Offset(0, 2).Value = budget
Else
Target.Offset(0, 2).Value = ""
End If
so if I have nothing in column D or "Amount" column then
column F cells should get blank but its not working am I doing
right
 
M

Mike H

You need a couple of changes to do that, try this but remember if you select
multiple cells and delete the macro doesn't work because it doesn't know
which is the target cell but that only matters if you delete anything in
column D

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("D5:D20")
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D5:d20")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0, 1).Value,
Range("j5:n20"), 5, False)
For Each c In MyRange
If c.Address <> Target.Address Then
If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value <> "" Then
Target.Offset(0, 2).Value = budget
Else
Target.Offset(0, 2).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub
 
K

K

You need a couple of changes to do that, try this but remember if you select
multiple cells and delete the macro doesn't work because it doesn't know
which is the target cell  but that only matters if you delete anything in
column D

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("D5:D20")
   If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("D5:d20")) Is Nothing Then
        If IsNumeric(Target) Then
            On Error Resume Next
            Application.EnableEvents = False
            budget = WorksheetFunction.VLookup(Target.Offset(0, 1).Value,
Range("j5:n20"), 5, False)
For Each c In MyRange
    If c.Address <> Target.Address Then
        If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then
           budget = budget + c.Value
        End If
    End If
Next c
           If Target.Value <> "" Then
                Target.Offset(0, 2).Value = budget
            Else
           Target.Offset(0, 2).Value = ""
           End If      
                Application.EnableEvents = True    
            On Error GoTo 0
        End If
    End If
End Sub





...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...
Thanks lot Mike. you dont know how thankful i am to you because you
have solved my biggest problem as i am doing project and i needed this
macro to finish it. Thanks again.
Just small question. can you suggest for me any thing that how can i
become good in creating macros. like if you know any book or website.
Have you studied about this or just learned yourself because you are
quite good
 
K

K

You need a couple of changes to do that, try this but remember if you select
multiple cells and delete the macro doesn't work because it doesn't know
which is the target cell but that only matters if you delete anything in
column D

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("D5:D20")
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D5:d20")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0, 1).Value,
Range("j5:n20"), 5, False)
For Each c In MyRange
If c.Address <> Target.Address Then
If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value <> "" Then
Target.Offset(0, 2).Value = budget
Else
Target.Offset(0, 2).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub





...

read more >>- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks lot Mike. You don't know how much I am thankful to you because
you
have solved my biggest problem as I am doing project for my work and
this macro has completed my work. Thanks again.
Just small question that can you please suggest me anything that how I
can
become good in creating Macros. Is there any website or Book you can
recommend me. Have you studied about this or just learned it yourself
as you
are quite good in this
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy and Save in specific way 1
Lookup formula needed 1
MACRO OR FORMULA NEEDED FOR CALCULATION 2
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
NEED MACRO OR FORMULA 4
Help with this formula 4
Formula to return column header 2
How to temp disable Worksheet_Change Event while code in same completes? 4

Top