SOLVED

Running macros in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3079034%22%20slang%3D%22en-US%22%3ERunning%20macros%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3079034%22%20slang%3D%22en-US%22%3E%3CP%3EI%20defined%20a%20macro%20in%20Excel%20for%20a%20cell.%3C%2FP%3E%3CP%3EWhen%20I%20try%20to%20run%20it%20again%2C%20it%20runs%20on%20the%20same%20cell.%3C%2FP%3E%3CP%3EHow%20can%20I%20make%20it%20run%20for%20all%20the%20remaining%20cells%20of%20the%20spreadsheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3079034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3079283%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20macros%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3079283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159986%22%20target%3D%22_blank%22%3E%40bosinander%3C%2FA%3E%26nbsp%3BHi%2C%3C%2FP%3E%3CP%3EIs%20there%20any%20other%20way%20(that%20avoids%20coding)%3F%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20selected%20a%20range%20of%20cells%20and%20tried%20to%20run%20the%20macro%2C%20but%20it%20runs%20on%20only%201%20cell%20(the%20original%20one).%3C%2FP%3E%3CP%3EI'm%20wondering%20if%20it's%20related%20or%20not%20with%20%22Use%20Relative%20References%22%3F%3C%2FP%3E%3CP%3EBtw%2C%20I%20couldn't%20find%20a%20way%20to%20turn%20that%20feature%20ON.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3079137%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20macros%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3079137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1289863%22%20target%3D%22_blank%22%3E%40Romi_Diudea%3C%2FA%3E%26nbsp%3BHi.%3C%2FP%3E%3CP%3EYou%20need%20to%20loop%20through%20the%20range%20of%20cells%2C%20eg%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20forEachCell()%0A%20%20%20%20Range(%22a1%22).Select%0A%20%20%20%20For%20Each%20cell%20In%20ActiveCell.CurrentRegion%0A%20%20%20%20%20%20%20%20'do%20your%20thing%2C%20eg%0A%20%20%20%20%20%20%20%20cell.Select%0A%20%20%20%20%20%20%20%20cell.Font.Bold%20%3D%20True%0A%20%20%20%20Next%20cell%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

7 Replies

@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

@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.

@Romi_Diudea 

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

bosinander_0-1643371804967.png

 

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.

@Romi_Diudea 

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;

bosinander_0-1643380583706.png

 

@bosinander We're getting really close!
What kind of escape character(s) could mimic the behavior of the ALT + Enter key combination?
I need to have a cell like this (all in one single cell):
First Name John
Last Name Doe
Amount 1
best response confirmed by Romi_Diudea (New Contributor)
Solution

@Romi_Diudea 

You 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.