Results for "sum values specific range"

First proposal :

Good afternoon Jeremie,

Are you French? If so, why are you posting in the English section of this forum?

Anyway, can you share your file or a part of it, so that we can help you more easily?

Hi everybody ! I am pleased to be a new member of this group that has helped me so much trough many of my projects.

Here is my problem. I want to do the sum of values in a specific range of cells. In the picture provided, I would like to know the total value of "CAL JAUNE 20 X 50' SP". So I'd need to find all the cells that contain the value "CAL JAUNE 20 X 50' SP" in the B column and do the sum of all the cells in the same row but in the E colum. I'll have to do that with a bunch of other codes. Also the SUM of "CAL JAUNE 20 X 50' SP" has to be in cell A1 of sheet 2, the SUM of "CAL 100' HARM MOKA/6BTE" in cell B1 of sheet 2... The quantity of CAL JAUNE 20 X 50' SP is going to change as they are produce, so that's why I need to identify the cells with this value in it.

Thanks a lot in advance, J !:)

Aha merci !! Mais bien sûr comment ai-je pu ne pas y penser plus tôt !!

Bonne nouvelle année à vous Curulis !

Hello Jeanne,

When you want to delete rows in the same time you're looking after items, you have to write your FOR-NEXT code from bottom to the top of the rows!!
Try this!

Have a happy new year!

Hi, I hope you are well,

I am encountering a problem with a function/code that I built in order to:

Go through the rows of a sheet one after the other and :

-Copy the rows in which the first cell contains a string,

-Paste the rows in a second sheet

-Delete said rows on the parent file

The thing is, this code is partly working, some of the rows which contain the string are indeed copied and pasted but some arent ! And I am not understanding why.

The rows that are sucessfully copied also are not the same ones when I rearrange their place in the sheet

See below my code

My code:

Sub ctrlcctrlv()

Index1 = 1 'index for selecting parent sheet where to find rows
Index2 = 2 'index for sheet where to paste the rows

IndexL2 = 1 'index for pasting rows one after the other on sheet 2

anomalie = "example" 'the string I'm looking for

For Each rw In Worksheets(Index1).Rows
libellé = rw.Cells(1, 1) 'the string in first cell of my row for which I want to check if it contains the string I'm looking for
If InStr(1, libellé, anomalie) <> 0 Then
rw.Copy Worksheets(Index2).Rows(IndexL2)
rw.Delete
IndexL2 = IndexL2 + 1
End If
Next

End Sub

edit moderation : code put between code tags, using "</>" button on the menu bar. Please make sure to use it for future messages.

Thank you very much for anyone who will take the time to answer.

I think i might change methods all over ... :[ maybe look for my string in the whole column directly and store the indexes that come up :(

Sincerely,

Good evening corriedtoppin,

Would a scale like this one be better for what you want to do?

Let me start over.

Good day excel world.

I have a concern.Below link have a list of countries that carries each country unique data.

Consumer Confidence - Countries - List

Now let's take the 1st two example:

#1 Country: Albania Data: -13.60
Below is Albania historical values 
snapshot:https://imgur.com/C3GjVvBIf 
you notice all there historical data are measure with a (-) number.
https://tradingeconomics.com/albania/consumer-confidence
Albania Consumer Confidence

and

#2 Country: Argentina Data: 42.40
Below is Argentina Historical values 
snapshot:https://imgur.com/ECIbh1j
If you notice all there historical data are measure with a positive number.
https://tradingeconomics.com/argentina/consumer-confidence
Argentina Consumer Confidence

My problem is this:

How can I use 3-Color Scale "Heat map", using conditional formatting, to show it correct color code for each country?

Below is the WRONG EXAMPLE of conditional formatting use

directly.

What do you suggest I do?

Hello Excel World.

I'm in the process of creating a 3-Color Scale "Heat map", using conditional formatting for investment purposes.

But I am having some challenges in creating the heat map, because each data carries there own unique values and may differ extremely from each other.

See Link below, of countries listings and the values outline under the header "LAST"

https://tradingeconomics.com/country-list/consumer-confidence

I also thought with myself. If the heat map should work effectively, each value or each cell reference should have there own heat map values.

But what is the effective way in creating these values or heat map ratio.

See link below of one of the example given.

https://tradingeconomics.com/albania/consumer-confidence

In conclusion.

What is the best direction to create this?

What are your experiences if any?

How do you want me to proceed?

Thanks in advance

corriedtoppin a écrit :

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

Hello,

That one look like a valid full name. If that's not working anymore, I can't help you.

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

I don't know. That's the name of the excel file: Countries Indicators #1 NSB (1)

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1).xlsx

End Sub

I am only getting and error.

corriedtoppin a écrit :

This is the file name:

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1)

End Sub

This is still not a valid filename...

The right syntax is simple : Complete Path + \ + Workbook name + Extension

I gave you an example : C:\User\FullPath\WorkbookName.xlsx

Your filename doesn't look like to this one !

This is the file name:

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1)

End Sub

Ohhh. I'm sorry going to try

You are just reading half part of my responses...

Pedro22 a écrit :

Filename may include full path (like C:\Users\Corey\Desktop\Anton Heatwave\PMI\) and workbook name (like WorkbookB.xlsx).

Your actual filename is not valid.

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Filename to adapt

End Sub

The above code

corriedtoppin a écrit :

The test code don't seems to work.

Error states: Compile error: Syntax error.

How can I get around this?

Paste here the code you tested, please.

I'm not a soothsayer !

The test code don't seems to work.

Error states: Compile error: Syntax error.

How can I get around this?

Filename may include full path (like C:\Users\Corey\Desktop\Anton Heatwave\PMI\) and workbook name (like WorkbookB.xlsx).

You can just test this code to be sure that the workbook is correctly opened :

Sub Test1()

    Workbooks.Open(Filename:="C:\User\FullPath\WorkbookName.xlsx") 'Filename to adapt

End Sub