Everything done below highlighted row

tropics123

Board Regular
Joined
May 11, 2016
Messages
85
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.
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.



Code:
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
 
Last edited by a moderator:
Upvote 0
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.



Region CreditJanFebTypeCreditTypeMarFebJunTypeSep
N220550850322500400500600450300250
S35025015012513040025022527535050
W400350250220350600750700800200500
Should beCreditBlankBlankTypeCreditTypeBlankBlankBlankTypeBlank

<tbody>
</tbody>
 
Upvote 0
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 :)

1columns A -->LMNOPQR
2NameHeaderTypeCreditTypeCreditTypeCredit
3Bobinfo1-8004-4201-486
4Susaninfo2-2501-5105-281
5Mikeinfo5-3101-1402-334
6FF (yellow highlight line starts here)
7BobinfoFinance800Finance420Finance486
8SusaninfoFinance250Finance510Finance281
9MikeinfoFinance310Finance140Finance334
10FFFinanceFinanceFinance

<tbody>
</tbody>
 
Upvote 0
see if this enters the "Finance" portion.

Please make a copy of your data.

Code:
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
 
Last edited:
Upvote 0
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...
Code:
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top