Posts by HestiA

Hi KjBox,

Damn thats a genius way of using SUMIF! I was actually figuring out how to fit a "YEAR" function in a SUMIF formula before, but i gave up afterwards and went to SUMPRODUCT haha. The formulae works now with your trick XD

Thanks for the help guys!

~ Hestia

Hi carim,

I have tried the array formula that you have recommended, but it still provides a #value error. Could it be because the values are referenced using a formula?

~ Hestia

Hi all,

I have yet another burning question, this time regarding sumproduct. I am having problems getting my sumproduct formula to work, as they would show me a #value error when my referred range has null values.

Here is my sumproduct formula:

=SUMPRODUCT((YEAR($K$3:$K$43552)=D3)*($L$3:$L$43552))/12

I am using a two dimensional sumproduct formula, which helps me detect which data belongs to that particular year (matching the year in D3) and sum up all those values.

Attached is a similar version of my main working file, however, i use index and match in my main working file to reference the data instead of isblank.

Is there anyone that could help me with this? Sorry if the explanation is rather bad.

Thanks

~ Hestia

Hi all,

Sorry I got it solved. Turns out that I was using a cursive " " for the lower half of my codes, so half of my codes did not register and copy pasted the data based on cell destination rather than finding the correct reference.

Sub TransferData()


Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String


lastrow1 = Sheets("Workingsheet").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To lastrow1
myname = Sheets("Workingsheet").Cells(i, "A").Value


Sheets("CopyPasteSheet").Activate
lastrow2 = Sheets("CopyPasteSheet").Range("A" & Rows.Count).End(xlUp).Row


For j = 2 To lastrow2


If Sheets("CopyPasteSheet").Cells(j, "A").Value = myname Then
Sheets("Workingsheet").Activate
Sheets("Workingsheet").Range(Cells(i, "B"), Cells(i, "CF")).Copy
Sheets("CopyPasteSheet").Activate
Sheets("CopyPasteSheet").Range(Cells(j, "B"), Cells(j, "CF")).Select
ActiveSheet.Paste
End If


Next j
Application.CutCopyMode = False
Next i
Sheets("Workingsheet").Activate
Sheets("Workingsheet").Range("A1").Select
End Sub

Here is the code if anyone needs it.

Have a nice day! (p.s. will stop spamming this thread alr haha)

~ Hestia

Hi all,

I tried following a video online using this code:

Sub TransferData()


Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim timestamp As String


lastrow1 = Sheets("Workingsheet").Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To lastrow1


timestamp = Sheets("Workingsheet").Cells(i, "A").Value


Sheets("CopyPasteSheet").Activate


lastrow2 = Sheets("CopyPasteSheet").Range("A" & Rows.Count).End(xlUp).Row


For j = 2 To lastrow2


If Sheets("CopyPasteSheet").Cells(j, "A").Value = timestamp Then


Sheets("Workingsheet").Activate


Sheets("Workingsheet").Range(Cells(i, "B"), Cells(i, "CF")).Copy


Sheets("CopyPasteSheet").Activate


Sheets("CopyPasteSheet").Range(Cells(i, "B"), Cells(i, "CF")).Select


ActiveSheet.Paste




Next j


End If


Application.CutCopyMode = False


Next i


Sheets("Workingsheet").Activate


End Sub

However, this only copies and pastes the row of data in the exact cell destination on the next sheet.

Is there anyone that could help me with this please?

Thanks

I think my explanation is not that clear, hence here is a more detailed explanation regarding my question.

I have 6 rows of data drawn from a web query, and pasted into the "working sheet". These 6 rows include dates in the format of (mmm-yyyy) in column A, different countries in each column and values belonging to the different countries.

In the "copy paste sheet", i have historical data which follows the same format as the working sheet.

What I would like to do is:

If any of the dates for the six rows of data in "working sheet" = any of the dates in "copy paste sheet", then copy all of the row's data (starting from column B) from "working sheet" and paste it onto the row with matching dates in "copy paste sheet". It is like a vlookup but using vba, and i am not sure how to code it as well.

However, there might be new countries in the columns of working sheet. Besides showing the number of new countries, I would like the macro to show which are the new countries.

Hopefully this is a clearer explanation.

~ Hestia

Hi Carim,

Yes I tweaked the codes to my settings and now it works perfectly!

Here is how it looks now:

Sub updatecopypastesheet()


Dim x As Long
Dim y As Long
Dim lastC1 As Long
Dim lastC2 As Long


Dim wbtestdata As Workbook
Dim workingsheet As Worksheet
Dim CopyPasteSheet As Worksheet
Dim confirmationRetrieve As Long


'comfirmation box
confirmationRetrieve = MsgBox("Do you want to check data?", vbYesNo + vbInformation, "Retrieve Confirmation")


If confirmationRetrieve = vbYes Then
    GoTo updatecopypastesheet
Else
    Exit Sub
End If


updatecopypastesheet:


Set wbtestdata = Workbooks("Web draw test file.xlsm")
Set workingsheet = wbwbtestdata.Sheets("Working sheet")
Set CopyPasteSheet = wbwbtestdata.Sheets("Copy Paste Sheet")


lastC1 = workingsheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
x = lastC1
lastC2 = CopyPasteSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
y = lastC2
  If x = y Then
    MsgBox " No New Countries Found"
  Else
    MsgBox x - y & " New Countries Available, Please Update Accordingly"
  End If
  
End Sub

However, I would also like to include a code which detects which is the new column of data and paste the data accordingly to the dates. Is it alright if you could help me with this too please?

Thanks for your help again!

~ Hestia

Hi Carim,

Thanks for your code! I will put it out to testing and see if it works!

~ Hestia

Hi all,

Here is the file that I am working on currently.

I would like to actually copy data from cells J18 to O73 and copy them according to the months on J14:O14 and also based on countries on column B in "Data Draw sheet". Then I would like to paste them accordingly into the "Working Sheet" (eg: Apr-18 Argentina on cell C221). I have already done a macro to copy and paste the values i needed.

However i am not sure how to detect if there are any new countries added in column B, then show a notice of the countries that are new.

Is there anyone that could help me with this?

~ Hestia

Hi Carim,

Thanks for the answer above. I will try making a macro following these comments.

If i have any problems regarding it, will post it here

~ Hestia

Hey Carim,

Sorry for the reply! Yes indeed it is working wonderfully!

Thanks for your help once again XD

~ Hestia

Hi all,

I have yet another burning question regarding macros this time. I am trying to build a macro which detects a new row of data added, which are data drawn from a web query.

This web query draws rows of data based on countries, and there might be new countries each time it is refreshed in the workbook. I would like to use my copy and paste macro to paste country data into another sheet in the same workbook. However, my copy paste macro only pastes data based on the top row countries (eg A1 = Argentina), but does not detect if there are new country data drawn from the web query.

Is there anyone that knows how to build a macro which detects the new country row?

Also, I would like the macro to show a notification to tell the user that there is a new country found (e.g. Country A is new, please insert a new column).

Thanks!

~ Hestia

Ozgrid sure knows how I feel haha.

Heres all the thanks :flower:

(P.S. Ozgrid pls fix your host server connection, its very laggy thanks!)

Hi Carim,

The formulas are working perfectly!

Thanks for the help yet again XD

~Hestia

Hi Carim,

The formulas are working perfectly!

Thanks for the help yet again :thumbcoo:

Hi Carim,

The formulas are working for most! However i have some problems when trying to drag down the formula. It shows me the #NUM error. Any reason why it is a numerical error?

Thanks for the help yet again :thumbcoo:

Hi Carim,

The formulas are working perfectly!

Thanks for the help yet again :thumbcoo:

Hi Carim,

Whoops, it seems like ozgrid was lagging a bit yesterday and didnt register the file I uploaded.

Here is the file!

~ Hestia

Hi all again,

I have yet another question regarding index and match formulas. I would like to draw a set of data based on the year and month of the date data.

I am not really good at explaining this, so i have attached a sample file of what i will like to do. I will like to use index and match to draw data from the volume column in sheet 1, and it is based on the date column in sheet1 as well. Then i would like to have this data displayed on the monthly table in sheet 2.

Is there anyone that can help me with this?
Thanks!

Hey Carim,
Hahahaha, tricks huh? Alright now i know who to find if i have any problems with those charts :lol: :congrats:

Thanks for all the help once again

~ Hestia