Cell logic related!

Zshan

New member
Joined
Apr 28, 2020
Messages
31
Reaction score
0
Points
0
Excel Version(s)
Excel10
Hi everyone,

I am kinda new here & begginer I am working on a code but I need a little help!
My sheet requires two columns BASEAMOUNT & DATE

DATA SHEET = JACK_1
OUTPUT SHEET = JAMOUNTCOMING

JACK_1 CONTAINS "DATE AS COLUMN C " & "BASEAMOUNT AS COLUMN E "

JAMOUNTCOMING CONTAINS "TOT AMOUNT AS COLUMN B " & "DATE AS COLUMN C"


When I insert row in JACK_1 I want "DATE & BASEAMOUNT" copied in "TOTAMOUNT & DATE" at the end of the cells automatically same like row in JACK_1 but instead its bieng inserted in second last cells and also its affecting other columns in JAMOUNTCOMING which is not good.

the code I am using is

Code:
Sub JAMOUNTCOMING()
Worksheets("JACK_1").Range("E2").Offset(1, 0).End(xlDown).Copy
Worksheets("JAMOUNTCOMING").Range("B3").End(xlDown).Offset(1, 0).Insert
Worksheets("JACK_1").Range("C2").Offset(1, 0).End(xlDown).Copy
Worksheets("JAMOUNTCOMING").Range("C3").End(xlDown).Offset(1, 0).Insert

Application.CutCopyMode = False


End Sub

in this code source for values is fine means getting last values of cell but inserting is problem & its also not good for other columns.

If anybody could help me it'll be great!


h1.jpgh2.jpg
 
Last edited by a moderator:
Please use code tags around your code next time, I have done it for you today.
 
You don't need to copy and paste, you can simply assign the value. Try this:


Code:
Worksheets("JAMOUNTCOMING").Range("B3").End(xlDown).Offset(1, 0) = Worksheets("JACK_1").Range("E2").Offset(1, 0).End(xlDown)
Worksheets("JAMOUNTCOMING").Range("C3").End(xlDown).Offset(1, 0) = Worksheets("JACK_1").Range("C2").Offset(1, 0).End(xlDown)
 
Hi,
Thx for reply!
The code for assign didn't work it assigning values into "JAMOUNTCOMING" B5 & C5 even "JACK_1" values ranged to E9 & C9, What I want is if value in JACK_1 E5 & C5 or in bottom further then they appear in JAMOUNTCOMING B6 C6 & in bottom
Thx!
 
Ok, thought you wanted them at the bottom of the list on the destination sheet. If you want them offset one row down in the destination sheet, try this

Code:
Sub JAMOUNTCOMING()
Dim rng1 As Range


Set rng1 = Worksheets(1).Range("E2").Offset(1, 0).End(xlDown)
Worksheets(2).Cells(rng1.Row + 1, "B") = rng1


Set rng1 = Worksheets(1).Range("C2").Offset(1, 0).End(xlDown)
Worksheets(2).Cells(rng1.Row + 1, "C") = rng1


Application.CutCopyMode = False


End Sub
 
Hi,
I'm very thankful to you.
your reply was much needed!

Now I'd like to ask you how'd you write it in 'if,or,and,then' conditions!

I mean if I insert a row in "JACK_1" the value in cell "E" is > previous then it should be copied in "JAMOUNTCOMING" "B" with date refernce otherwise nothing changes in "JAMOUNTCOMING"

if I insert a row in "JACK_1" the value in (cell "G" is) > previous of "E" where (current "E") is still < (previous "E") then it should be copied in "JAMOUNTCOMING" "B" with date refernce otherwise nothing changes in "JAMOUNTCOMING"

if I insert a row in "JACK_1" the value in (cells "F" + "G" is) > previous of "E" where (current "E") is still < (previous "E") then it should be copied in "JAMOUNTCOMING" "B" with date refernce otherwise nothing changes in "JAMOUNTCOMING"

In these three conditions what would be your suggestion!

I hope I'm not bothering just asking for favour!

Again Thx.
 
A couple of questions:

When you say "insert a row" do you mean enter data in the empty row at the bottom of the data?

When you say "previous" do you mean the cell immediately above the new entry? That is, if you enter a new value in E6, is previous E5? Or is it all of the data that's been entered, E3 to E5?
 
Hi,
Yes of course, enter data in the empty row at the bottom of the data!
if I enter a new value in E6, previous is E5!
if I enter a new value in E7, previous is E6! & so on.
 
Ok, try this

Code:
Sub JAMOUNTCOMING()
Dim rngPrevious As Range, rngE As Range, rngF As Range, rngG As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim dest_row As Long


Set ws1 = Worksheets("JACK_1")
Set ws2 = Worksheets("JAMOUNTCOMING")


Set rngE = ws1.Range("E2").Offset(1, 0).End(xlDown)
Set rngPrevious = rngE.Offset(-1, 0)
Set rngF = rngE.Offset(0, 1)
Set rngG = rngE.Offset(0, 2)
Set rngDate = rngE.Offset(0, -2) 'date in col C
dest_row = rngE.Row + 1


If rngE > rngPrevious Then
    ws2.Cells(dest_row, "B") = rngE
    ws2.Cells(dest_row, "C") = rngDate
ElseIf rngG > rngPrevious Then
    ws2.Cells(dest_row, "B") = rngG
    ws2.Cells(dest_row, "C") = rngDate
ElseIf rngF + rngG > rngPrevious Then
    ws2.Cells(dest_row, "B") = rngF + rngG
    ws2.Cells(dest_row, "C") = rngDate
Else
    ' do nothing
End If


Application.CutCopyMode = False


End Sub
 
Hi,
Thanks again for reply but its not working!

I mean conditions are not working & every value is being copied no matter if it's greater or lesser!
& it's also affecting dates in column "c"

I've attached example file if you could check.

Thx!
 

Attachments

  • Reply.xlsm
    24.1 KB · Views: 6
It looks like the minus signs were missing in these two lines

Code:
Set rngPrevious = rngE.Offset(-1, 0)
...
Set rngDate = rngE.Offset(0, -2) 'date in col C
 
Hi,
I'm really thankful to you it'b very difficult for me to write such code!
lots of thx buddy.

For testing it I put the code you mentioned!

It gave me Pic1!

Now I noticed three issues.

1st,
VALUES appeared in JAMOUNTCOMING are not right below the (filled cell means non blank cell) there are empty cells which would affect other columns calculations related to those Values & that'd be incorrect.

2nd,
E7 = 630 in "JACK_1" < F5 + G5 = 670 in "JACK_1" but it appeared in B8 IN "JAMOUNTCOMING" it'll also be incorrect with other calculatios.

3rd,
If there's one more condition F12 + G12 > E12 (E12 = which is also > F10 + G10 = (PREVIOUS)) like in 8th Row of "JACK_1" in Pic1
AND in F17 + G17 > E17 (E17 = which is also > F12 + G12 = (PREVIOUS)) in "JACK_1" in Pic1 & so on.

So I add ElseIf in Pic2.

But It gets me to Pic3, seems to push me middle of no where!

There is also one more thing whats in C9,C11 & C18 in "JAMOUNTCOMING" Why numbers are there instead of dates. Those are the same numbers when we wanna Format Cells; in General category those come in samples!

Pic format has some problems so I uploaded pics in PDF version.

Would you take a look and & reply.

I'm very sorry I'm keep asking you questions, I hope its not a problem.

Really Thx!View attachment Pic1.pdfView attachment Pic2.pdfView attachment Pic3.pdfView attachment Pic1.pdfView attachment Pic2.pdfView attachment Pic3.pdf
 
I'm happy to help but can't continue without having a better idea of what you are trying to do. First, can you explain in words what the spreadsheet is supposed to do? Second, can you provide a workbook with the results you want to see. A full list of data in JACK_1 and the results that you want to see in JAMOUNTCOMING.

Take a look at this to understand the importance of clearly defining the problem. You'll find a lot of other useful information on that site.

https://sites.google.com/site/beyondexcel/project-updates/advancedexcelmodeling-1
 
Hi,
Thx for website its good,

Actually my spreadsheet has mixed calculation of staff salaries,working hours,taxes,bonuses,fixed & variable cost of productions & labour costs,exchange rates etc, anyway!

The problems I was facing were those logics for related cells which you helped me out with!
Now just 2,or 3 issues left what I asked you to take a look at!
like Values are not appearing right below the cells in "JAMOUNTCOMING",active " E" cell > "E" right above but "F" + "G" in active Row is more > active "E" and other ones.

So what happened did you check Pic1 & the condition I put in Pic2 whats wrong with that!

again Thx for help!
 
I know that you want to solve your problem but I'm having a hard time understanding what it is you are trying to do. Please take this workbook, fill in the yellow cells on the JAMOUNTCOMING sheet with the correct values that you want the code to place in those cells, and repost it.
 

Attachments

  • ReplyToBeFilledIn.xlsm
    21.6 KB · Views: 5
I hope I can answer your question to the best of my understanding.

Your question about greater or lesser, if the previous value is ABOVE the current value, then you need to go 1 cell up which equates to (-1, 0)

Set rngPrevious = rngE.Offset(-1, 0)

Your question about dates in column "C", If you're reference range is "E", then you need to move 2 cells to the left which equates to (0, -2). Hope this answer's your question with dates.

Set rngDate = rngE.Offset(0, -2)

Correct me if I'm wrong but in Offset, based on your reference point, going UP and LEFT is "-" while going RIGHT and DOWN is "+"
 
Hi,
I'm sorry I couldn't explain myself properly!

Hopefully this'd make myself clear!
Thx.
 

Attachments

  • ReplyWithFilled.xlsm
    18.6 KB · Views: 7
Thank you, that was very helpful. I edited the NORMS_1 macro and believe it will do what you want.
 

Attachments

  • ReplyWithFilled_RevisedCode.xlsm
    22.4 KB · Views: 9
Hi,
Thx for the code what you have done that means a lot!

I usually cant write that kinda advanced code.

I also wanted to ask you that I wrote in details


````6, E18 in JACK_1 is greater than B9 in JAMOUNTCOMING but its lesser than D10 in JAMOUNTCOMING so its not appeared in B10``````


so here which part of the code belongs to that statement could you point out please!

Thx & sorry for keep bothering you.
 
Back
Top