Jan 28 2022 02:43 AM
I defined a macro in Excel for a cell.
When I try to run it again, it runs on the same cell.
How can I make it run for all the remaining cells of the spreadsheet?
Jan 28 2022 03:05 AM
@Romi_Diudea Hi.
You need to loop through the range of cells, eg
Sub forEachCell()
Range("a1").Select
For Each cell In ActiveCell.CurrentRegion
'do your thing, eg
cell.Select
cell.Font.Bold = True
Next cell
End Sub
Jan 28 2022 03:44 AM
@bosinander Hi,
Is there any other way (that avoids coding)?
For example, I selected a range of cells and tried to run the macro, but it runs on only 1 cell (the original one).
I'm wondering if it's related or not with "Use Relative References"?
Btw, I couldn't find a way to turn that feature ON.
Jan 28 2022 04:13 AM
Depends on what you want to do, ie if it is applicable on a range or if it is needed to be evaluated cell by cell.
Maybe relative refences will do it for you.
"Use Relative Refences" is located in the first group on Developer Menu/Tab
Jan 28 2022 05:17 AM - edited Jan 28 2022 05:19 AM
I want it to work as follows:
- record a macro that takes the content of various columns within the 1st row and process the info (let's say, concatenate, copy & paste etc)
- run the macro on the next 700 rows (expecting the same behavior like the 1 st row, based on which I created the macro)
Looks like pretty basic.
Jan 28 2022 06:37 AM
Well, it seems to me not all too easy to set up the macro recording function but I am sure microsoft hase tried since it seems like a common need.
What is a native possibility with workbook techninque is to do much like it - cannot know if this is what you reaaly need but it is a way to without macro programming do a lot more than possible before Excel 365.
Three different concatenations of the names;
Jan 28 2022 07:23 AM
Jan 28 2022 08:47 AM
SolutionYou can use alt+enter between quotation marks, ie include the line feed as any other character.
="First Name " & C2 & "
Last Name " & D2 & "
Amount " & E2
You can also do it using its ASCII code, 10*
="First Name " & C2 & CHAR(10) & "Last Name " & D2 & CHAR(10) & "Amount " & E2
...but I prefer the first one being more visible and understandable for users familiar with alt+enter.
You may also find it by typing Ctrl+J in the find dialog (J is number ten in alphabet).
You may have to press Ctrl+U to easier see the full formula in Excel since it covers multiple lines.
* see eg https://www.ascii-code.com/ for more info on ASCII codes.
Jan 28 2022 08:47 AM
SolutionYou can use alt+enter between quotation marks, ie include the line feed as any other character.
="First Name " & C2 & "
Last Name " & D2 & "
Amount " & E2
You can also do it using its ASCII code, 10*
="First Name " & C2 & CHAR(10) & "Last Name " & D2 & CHAR(10) & "Amount " & E2
...but I prefer the first one being more visible and understandable for users familiar with alt+enter.
You may also find it by typing Ctrl+J in the find dialog (J is number ten in alphabet).
You may have to press Ctrl+U to easier see the full formula in Excel since it covers multiple lines.
* see eg https://www.ascii-code.com/ for more info on ASCII codes.