Everything done below highlighted row

[tropics123]

Hi! Any help on this would be greatly appreciated.

For example, row 89 is highlighted yellow but it can vary depending on the different data received daily. Everything above the highlighted yellow row are data and below the yellow highlighted row are all empty cells. Above the yellow highlighted row are many columns filled with data, and a lot of the columns have repeated headers. For example, there will be multiple columns with the header names "Type" and "Credit". I need some help with #2 & #3 below I greatly appreciate your help.

1) Above the yellow highlighted row: copy all data from columns A-L and paste it in the first empty cell in column A below the highlighted row. This one I can do the macro for.

2) If header name is "Type" (and may appear in multiple columns), put in word "Finance" in the same column directly below the yellow highlighted row and go all the down to match with the number of rows in column A (the word "Finance" can only start below the yellow highlighted row)

3) Copy all amounts in columns with header "Credit" and change the amounts to a positive number. The header "Credit" might be in multiple columns and need to find them all, and paste it directly in its respective columns below the yellow highlighted row.

 

[billandrew]

I believe this will perform what you have requested in #2 and part of #3 . I am sure one the the experts here will jump in to assist,as I am unsure how to convert or copy the negative values in the respective column to a positive.

Sub ChangeType()
Dim Finalrow As Long, i As Long
For i = 1 To 12
Finalrow = Range("A" & Rows.Count).End(xlUp).Row
If Cells(1, i).Value = "Type" Then
Cells(Finalrow + 1, i).Value = "Finance"
End If
    If Cells(1, i).Value = "Credit" Then
    Cells(Finalrow + 1, i).Value = "Credit"
    End If
Next
End Sub

 

[tropics123]

Hi Bill,
I tried the code you gave but it didn't do anything

 

[billandrew]

Maybe I do not understand what you require.

Question 1 - You indicated you have this resolved.

Question 2 - The code supplied will insert "Finance" in the same column in the last row and will insert "Type" in the same column in the last row.



[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Region [/TD]
[TD]Credit[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Type[/TD]
[TD]Credit[/TD]
[TD]Type[/TD]
[TD]Mar[/TD]
[TD]Feb[/TD]
[TD]Jun[/TD]
[TD]Type[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]220[/TD]
[TD]550[/TD]
[TD]850[/TD]
[TD]322[/TD]
[TD]500[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD]600[/TD]
[TD]450[/TD]
[TD]300[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]350[/TD]
[TD]250[/TD]
[TD]150[/TD]
[TD]125[/TD]
[TD]130[/TD]
[TD]400[/TD]
[TD]250[/TD]
[TD]225[/TD]
[TD]275[/TD]
[TD]350[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]W[/TD]
[TD]400[/TD]
[TD]350[/TD]
[TD]250[/TD]
[TD]220[/TD]
[TD]350[/TD]
[TD]600[/TD]
[TD]750[/TD]
[TD]700[/TD]
[TD]800[/TD]
[TD]200[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Should be[/TD]
[TD]Credit[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Type[/TD]
[TD]Credit[/TD]
[TD]Type[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Type[/TD]
[TD]Blank[/TD]
[/TR]
</tbody>[/TABLE]

 

[tropics123]

Sorry, what I wrote was a little confusing. Hopefully, this table will do a better job at explaining my intentions. If the word "Type" appears in any of row 2 (the header row), then below the yellow highlighted line (row 6), put in the word "Finance" and go all the way down matching with column A. And if there are any text with word "Credit" in the header (row 2), then copy those numbers (change to positive numbers) and paste below yellow highlighted row (row 6). I hope this makes sense. Thank you

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]1[/TD]
[TD]columns A -->[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name[/TD]
[TD]Header[/TD]
[TD]Type[/TD]
[TD]Credit[/TD]
[TD]Type[/TD]
[TD]Credit[/TD]
[TD]Type[/TD]
[TD]Credit[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]info[/TD]
[TD]1[/TD]
[TD]-800[/TD]
[TD]4[/TD]
[TD]-420[/TD]
[TD]1[/TD]
[TD]-486[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Susan[/TD]
[TD]info[/TD]
[TD]2[/TD]
[TD]-250[/TD]
[TD]1[/TD]
[TD]-510[/TD]
[TD]5[/TD]
[TD]-281[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mike[/TD]
[TD]info[/TD]
[TD]5[/TD]
[TD]-310[/TD]
[TD]1[/TD]
[TD]-140[/TD]
[TD]2[/TD]
[TD]-334[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]FF (yellow highlight line starts here)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bob[/TD]
[TD]info[/TD]
[TD]Finance[/TD]
[TD]800[/TD]
[TD]Finance[/TD]
[TD]420[/TD]
[TD]Finance[/TD]
[TD]486[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Susan[/TD]
[TD]info[/TD]
[TD]Finance[/TD]
[TD]250[/TD]
[TD]Finance[/TD]
[TD]510[/TD]
[TD]Finance[/TD]
[TD]281[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mike[/TD]
[TD]info[/TD]
[TD]Finance[/TD]
[TD]310[/TD]
[TD]Finance[/TD]
[TD]140[/TD]
[TD]Finance[/TD]
[TD]334[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]FF[/TD]
[TD][/TD]
[TD]Finance[/TD]
[TD][/TD]
[TD]Finance[/TD]
[TD][/TD]
[TD]Finance[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[billandrew]

see if this enters the "Finance" portion.

Please make a copy of your data.

Sub Test()
Dim lastrow As Long, lc As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
lc = Cells(2, Columns.Count).End(xlToLeft).Column
For i = 3 To lastrow
For x = 2 To lc
If Cells(2, x).Value = "Type" And Cells(i, 1) <> "" Then
Cells(i, x).Value = "Finance"
End If
Next
Next
End Sub

 

[Rick Rothstein]

I wasn't sure what your starting data row was (3 as shown in your posted sample or 2 because what you show as your first row looks like Excel's headers). I assumed the first name is on Row 2 (change to 3, or whatever, if that guess is wrong). Given that, and assuming your yellow highlighted row is the row below the last name and the column locations are as shown), then I believe this non-looping macro will do what you want...

Sub FinanceCredit()
  Dim LastRow As Long, LastCol As Long
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(StartRow, Columns.Count).End(xlToLeft).Column
  Range("A" & StartRow & ":A" & LastRow).Copy Cells(LastRow + 1, "A")
  Intersect(Rows(StartRow & ":" & LastRow), Columns("L").Resize(, LastCol - 11)).Copy Cells(LastRow + 1, "L")
  With Cells(LastRow + 1, "L").Resize(LastRow - StartRow, LastCol - 11)
    .Value = Evaluate(Replace("IF(ISNUMBER(@),IF(@>=0,""Finance"",ABS(@)),@)", "@", .Address))
  End With
End Sub

 

[billandrew]

My apologies, the above supplied code does not copy the original data set below as you requested (missed that) this will overwrite the values in the "Type" column with the text "Finance". Fortunately Mr. Rothstein (One of the Great minds) has come to the rescue.

 

[Rick Rothstein]

Fortunately Mr. Rothstein (One of the Great minds) has come to the rescue.

What a most kind and generous thing to say... while I don't actually agree with your assessment, I do appreciate the sentiment.

I made some assumptions based on the OP's posted data which I hope hold for his actual data (if it does, then my code should work for him).

 

[tropics123]

Hi Bill,

thank you so much for trying!