Say I have a spreadsheet where A1–>D1 have values of ABC, DEF, GHI, JKL, then below each of those headings is many rows of numbers. I want to set all values in column ABC to be changed to 0, DEF to 1, GHI to 2, JKL to 3.
Is there a slick way to do it via a macro, where it will read that first row, and based on what is there, it will make the appropriate change? I have to do this to a lot of spreadsheets, and the columns may be out of order, so thought a macro would save me a LOT of time.
Please do not duplicate questions → Change all values in a column based on heading.
If you wish to bring a question higher in visibility, simply edit the question and it will go to the top of the list…
Wasn’t trying to duplicate, figured since it had been a week with no reply on the other one, I probably didn’t ask it clearly enough so should ask again in a clearer fashion.
Thanks for such a quick reply though even though it wasn’t related to the question. Any thoughts on the actual question at hand?
Same situation, Edit original question for further clarification - Note the edited section. This also brings the question back to the top.
Had just been looking at the question again. Logically it does not make sense. You state:
where A1–>A4 have values of ABC, DEF, GHI, JKL, then below each of those headings is many rows of numbers.
A1–A4 are rows. How can there be rows of numbers below each? Probably should be A1–>D1. Also not clear on how the new value is determined? Are the rows below filled or are there empty cells? Possibly provide a sample showing what you have and what and how these changed to values are determined/assigned.
@ratslinger
Sorry, first time using this forum, wasn’t sure of the etiquette. Thanks for the guidance.
But let me answer your questions, you’re right A1 → D1, as in A1, B1, C1, D1, So think the top row of the spreadsheet where those are essentially column names/headers for the data below them.
In all those rows below the headers are numbers, which based on what’s in the “headings” i just want to hardcode all to the same value only in cells which currently have values below it. hopefully the link below works, on the left is what i have for example, on the right is what i want to accomplish.
screenshot of sample
the other thing is that the columns may be in different orders in different spreadsheets so it would have to be smart enough to read the headings, like ABC may not always be the first column
Will there be always 4 columns? Or there can be more and macro shall iterate over all columns with data below?
Still not answered clearly, how the value is specified. Appears that a macro should be executed which will ask for the column to be changed and also the value it should be changed to. If so, what matter is it for the column headings? Why not just change Column A values to 3? Or is it that heading ABC can be present in more than one column on a sheet or that you also want column heading ABC to be changed on multiple sheets with this same value?
@SM_Riga there could be more columns, I was just using four for my sample to try to see how it was possible. Ideally it would iterate over and depending what the first row contains it would then update cells below appropriately. I tried your macro you supplied below and it worked on my sample data!!
@Ratslinger I’m not sure which part isn’t clear for you. Maybe SM_Riga’s macro will help clear it up as that worked perfectly on my sample data I provided in a comment to you earlier? Let me try to say it a different way, if a column has a “heading” of ABC, i want all values below that heading to be changed to 0. DEF, change everything below to 1. the column headings signify what the values should be changed to.
Have withdrawn further comments. Appears based in you comment in answer you have all that is needed.
Hello @odtxahvm
Please find subroutine that loops through all sheets in the spreadsheet and changes all column’s cells content to the predefined value if the first row contains one of the predefined text string. Code is not much tested or optimized and it does not handle possible errors, so be sure to backup any file before running tests!
Sub ReplaceBasedOnColumnName
iSheetsCount = ThisComponent.Sheets.Count
For SheetCounter = 0 to iSheetsCount-1
oSheet = ThisComponent.Sheets(SheetCounter)
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(False)
iLastRowIndex = oCursor.rangeAddress.EndRow
iLastColumnIndex = oCursor.rangeAddress.EndColumn
For j = 0 to iLastColumnIndex
If iLastRowIndex = 0 Then
Exit For
End If
oColumnHeader = oSheet.Columns(j).getCellByPosition(0,0)
sHeaderText = oColumnHeader.GetString
Select Case sHeaderText
Case "ABC"
iNewCellValue = 0
Case "DEF"
iNewCellValue = 1
Case "GHI"
iNewCellValue = 2
Case "JKL"
iNewCellValue = 3
Case Else
iNewCellValue = "Not defined"
End Select
oColumnData = oSheet.Columns(j).getCellRangeByPosition(0,1,0,iLastRowIndex)
oContentCells = oColumnData.queryContentCells(31)
For k = 0 to oContentCells.Count - 1
If iNewCellValue = "Not defined" Then
Exit For
End If
oCellsData = oContentCells(k).DataArray
For i = 0 To UBound(oCellsData)
oCellsData(i)(0) = iNewCellValue
Next i
oContentCells(k).DataArray = oCellsData
Next k
Next j
Next SheetCounter
End SubTo answer your question above, there could be more columns, I was just using four for my sample to try to see how it was possible. Ideally it would iterate over and depending what the first row contains it would then update cells below appropriately. I tried your macro you supplied below and it worked on my sample data!! Just not sure if any other changes would be required if there are more columns like you questioned above.
thank you for the help!