PDA

View Full Version : adjusting code calling data in worksheet



maghari
02-20-2020, 05:18 AM
hello
i need help adjusting my code it calls data from sheet("data") to sheet ("sheet1")
Calling an accountant's registration, basic condition, registration number
I wrote the code and it works efficiently
But I want to add other conditions
If the account name is the same, whether a debtor(column j in sheet data) or a creditor(column k in sheet data) it is not repeated and the sum is collected when i search in sheet1 the range of search in sheet1 the range is( "g2")

i hope somebody help


Sub kid()
Dim Sh As Worksheet, ws As Worksheet, C As Range
Dim LR As Long, i As Long
'''''''''''''''''''''''''''''
Set ws = Sheet1: Set Sh = data2
'''''''''''''''''''''''''''''''''''
ws.Range("a5:i1000").ClearContents
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
LR = Sh.Range("c" & Rows.Count).End(xlUp).Row
i = 4
For Each C In Sh.Range("c5:c" & LR)
If C.Value = ws.Range("g2").Value Then
i = i + 1
ws.Cells(i, "B") = C.Offset(0, 3)
ws.Cells(i, "C") = C.Offset(0, 2)
ws.Cells(i, "D") = C.Offset(0, 4)
ws.Cells(i, "E") = C.Offset(0, 5)
ws.Cells(i, "F") = C.Offset(0, 6)
ws.Cells(i, "G") = C.Offset(0, 7)
ws.Cells(i, "h") = C.Offset(0, 8)
ws.Cells(i, "i") = C.Offset(0, 9)
End If: Next
'''''''''''''''''''''''''''''''''''''''''''''''''' End Sub
by the way i have ever issue this subject in this forum but no replying any one
https://www.mrexcel.com/board/threads/adjusting-code-calling-data-in-worksheet.1124694/#post-5430447

Leith Ross
02-22-2020, 02:11 PM
Hello maghari,

Since you have not illustrated for us both a before and after example nor mentioned the column headers, it is impossible for us to provide you with a solution.

Please list what is in each column and provide an example of the data before and after the criteria have been applied.

maghari
02-23-2020, 01:24 AM
Hello maghari,

Since you have not illustrated for us both a before and after example nor mentioned the column headers, it is impossible for us to provide you with a solution.

Please list what is in each column and provide an example of the data before and after the criteria have been applied.

thanks buddy for the replying

the main data in ("data2) begins from a5:k and the sheet(sheet1) begins from b5:h5 my code is really work when i fill in range("g2) in sheet1 based on CONSTRATION NO the exist in sheet(data2) in column c it calls specific data (e,f,g,h,i,j,k) from sheet("data") to sheet(sheet1) in column(b,c,d,e,f,g,h) i have the name of account whether credit the column(g) or debt the column (k) are in sheet(data2) are repeated what i would
when i call the data from sheet(data2) to sheet(sheet1) are collected the repeat name an sum the values

Leith Ross
02-23-2020, 02:56 PM
Hello maghari,

Thanks for posting the workbook. I have a few of questions.

1) What is CONSTRATION NO (number) ?

2) How does this effect the data copied to data2?

3) In your example you have a mismatch in Credits and Debits: CUSTODY OF ADAM, CAPTIAL. How should this appear on data2?