how to order multiple copies in other sheet into group/under a created header/row automatically?
With the code below, I want to order every time I copy to the other sheet to be organized like this.
Instead the code does this:
How can I change it?
Set findfirst = targetSheet.Range("H:H").Find("Tykkelse [m]") currentvalue = findfirst.Offset(1, 0).Value findfirst.Offset(-1, 0).EntireRow.Insert xlDown With Range(Cells(findfirst.Row - 2, 1), Cells(findfirst.Row - 2, 14)) .Merge .HorizontalAlignment = xlCenter .Interior.ColorIndex = 27 .Font.Bold = 1 .Font.Size = 18 .Value = currentvalue End With
See also questions close to this topic
Excel Formula: For every unique value in Col 1 and Col 2 - check for duplicates
Consider the following data. I have added help text across each data row to explain scenarios: Bill is Col A, Ref is col B and so on
Form: is Formula; Exceptns: With Current Formula the results I get and expected output is what I'm looking at
Bill Ref Ref2 HelpText Form Form Excptns Excepted output 123 557 123 Scenario1 1 1 FALSE FALSE 123 589 123-1 Scenario1 1 1 FALSE FALSE 123 591 123-2 Scenario1 2 1 TRUE FALSE 123 591 123-3 Scenario2 2 1 TRUE TRUE 124 432 124 Scenario1 1 1 FALSE FALSE 124 433 124-1 Scenario1 1 1 FALSE FALSE
Scenario1: Since they have same bill number and unique Ref Number, Ref2 column has to be unique
Scenario2: Since same bill number and same ref number, we can't have unique Ref2 values - therefore (it should be 123-2 and not 123-3 and I need to identify all such cases using excel formula)
Duplicates scenario will not exist as we are taking pivot
My effort:A B C D E F G Bill REf Ref2 HelpText Formula Formula Formul 123 557 123 Scenario1 =countIFS(A:A,A2,B:B,B2) 123 589 123-1 Scenario1 123 591 123-2 Scenario1 123 592 123-2 Scenario2
- I used countifs(A:A,A2,B:B,B2) in columnE
- countifs(B:B,B2,C:C,C2) in columnF
- columnG FORMULA is =AND(IF(E2>1,"TRUE","FALSE"),IF(F2=1,"TRUE","FALSE"))
when I FILTER Column G by true/false - true means exceptions: I get both instances as output. I'm howevr not interested at the first occurrence because it's valid. From there on, 2nd and incase 3rd instances - I will be interested
Please help me arriving at the formula of how can I can ignore the first occurrence. I can not use VBA. Only excel formulae - and no limit on column numbers
Excel: Repition of Answers using COUNTIF and VLOOKUP
Problems with this charity competition again...
To calculate the group standings I have used two formulas. Cell R11 Pic Cell R11 shows: =COUNTIF($AA$11:$AA$14,">"&AA11)+1+COUNTIFS($AA$11:$AA$14,AA11,$Z$11:$Z$14,">"&Z11)+COUNTIFS($AA$11:$AA$14,AA11,$Z$11:$Z$14,Z11,$X$11:$X$14,">"&X11)
This allows for the teams to be ranked in order of points, then goal difference then goals scored. I will hide this table in the final version of my sheet.
I have then used another formulation in AD11 which is: Cell AD11 Pic
This will be shown to the user and allows me to show the standings ranked in the correct order.
The problem I am having is that when all of the scores in the group are the same (for this example I have used 1-1 each time they play) it comes up with one winner and 2,3,4th place is the same and doesn't display the other two teams in the group.
Does anyone have any ideas? All help is greatly appreciated.
VBA - Run-time error '1004' - Method 'Range' of object'_Global' failed
Excuse me if you guys see this question again. However, I have searched for similar topics in this site but can't figure out my problem.
I have a VBA code snipet with a line throwing back an error:
Sub test() Dim rng As Range Set rng = Application.InputBox("Select range: ", "Select range", Type:=8) MsgBox (Range("rng").Rows.Count) End Sub
My intention is to prompt user to select a range and to count number of rows in that range.
If I pre-define the name range "rng" instead of selecting range in run-time like the code below, it will return the number of rows without errors.
Sub test() Dim rng As Range MsgBox (Range("rng").Rows.Count) End Sub
Could someone please tell me the problem with the range selected by user so that it can't return number of rows? Doesn't the "rng" range defined by user have a "Rows" property?
Thank you for your help.
Checking a box on a webpage
Hello guys, I need to check the box on a webpage that is shown in the first image and the get element by ID method does not work in this case:
How can I check the box? Also, in the second image is the code for the "Excel with formatting" box
Thank you in advance!
HTML parsing of website inspect element by VBA
I need to extract some image src data of the inspect element in website. i wrote some code, but it is not working .
Sub getSrcAttributeImgTag() Dim ie As InternetExplorer Dim html As HTMLDocument Dim ElementCol As Object, Link As Object Dim ecol As Long Application.ScreenUpdating = False Set ie = New InternetExplorer ie.Visible = False ie.navigate "http://test.site/showadv.php?rstr=0.8809384451371399" Do While ie.readyState <> READYSTATE_COMPLETE Loop Set html = ie.document Set ElementCol = html.getElementsByTagName(“img”) For Each Link In ElementCol ecol = Worksheets(“sheet1”).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column Cells(1, ecol).Value = Link.src Cells(1, ecol).Columns.AutoFit Next Set ie = Nothing Application.StatusBar = “” Application.ScreenUpdating = True End Sub
But this code not working for inspect element HTML.
Thanks in advance