+ Reply to Thread
Results 1 to 10 of 10

add items into specific location for adjacent cells based on helper columns for two sheets

Hybrid View

tubrak add items into specific... 04-28-2022, 06:18 AM
Sintek Re: add items into specific... 04-28-2022, 07:06 AM
tubrak Re: add items into specific... 04-28-2022, 08:06 AM
Sintek Re: add items into specific... 04-28-2022, 08:20 AM
tubrak Re: add items into specific... 04-28-2022, 09:15 AM
Sintek Re: add items into specific... 04-28-2022, 09:28 AM
tubrak Re: add items into specific... 04-28-2022, 11:50 AM
Sintek Re: add items into specific... 04-28-2022, 12:02 PM
tubrak Re: add items into specific... 04-28-2022, 12:12 PM
Sintek Re: add items into specific... 04-28-2022, 01:14 PM
  1. #1
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    229

    add items into specific location for adjacent cells based on helper columns for two sheets

    hello
    I have two columns in I,J for each separated sheet . the column I is the item should add into column B for specific sheet and the column J is where the location should add to it .
    so if I would add item into column B for adjacent cells based on adjacent cells column J . for instance when write 1 in column J then should add the item into position after first item and create one space after add it and if I write 0 should add before the first item . should add it to adjacent cell into column B and so on should search the position of item based on value in column J and add it after it .
    I put the expected result in sheet result how should be in sh1,sh2 into column B based on column I,J
    Attached Files Attached Files

  2. #2
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    O365 V2408
    Posts
    15,138

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    Sht1 rows 6-8 replacement data is not available?

    Sub J3v16()
    Dim Data, ws As Worksheet, i As Long
    For Each ws In Sheets
        With ws.Cells(1).CurrentRegion
            Data = .Value
            For i = 2 To UBound(Data)
                If .Parent.Range("J" & i) > 0 Then
                    Data(i, 2) = Application.Substitute(Data(i, 2), " ", " " & .Parent.Range("I" & i) & " " & .Parent.Range("J" & i).Value)
                Else
                    Data(i, 2) = .Parent.Range("I" & i) & " " & Data(i, 2)
                End If
            Next i
            .Value = Data
        End With
    Next ws
    End Sub
    Last edited by Sintek; 04-28-2022 at 08:19 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    229

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    thanks , but you seem to misunderstand what I want . as i said
    I have two columns in I,J for each separated sheet
    it should implement the helper columns for each sheet separately but your code implements column I,J is in first sheet for all of the sheets . this is wrong ,if you see the result you will find out different result for each sheet . and should implement one time not repeatedly when run macro more than time

  4. #4
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    O365 V2408
    Posts
    15,138

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    My bad...see amended code...References correct sheet...
    .Parent.

  5. #5
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    229

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    gives wrong result , may you see the highlighted items ? should not be existed
    Attached Files Attached Files

  6. #6
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    O365 V2408
    Posts
    15,138

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    Run and check...
    & , .Parent.Range("J" & i).Value)
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    229

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    yes this is great!
    just need prevent adding the item continuously if I run the macro more than one time accidentally.

  8. #8
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    O365 V2408
    Posts
    15,138

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    Start learning not to run macro twice...Or use helper cell...
    Sub J3v16()
    Dim Data, ws As Worksheet, i As Long
    For Each ws In Sheets
        If ws.Range("D1") = "" Then
            With ws.Cells(1).CurrentRegion
                Data = .Value
                For i = 2 To UBound(Data)
                    If .Parent.Range("J" & i) > 0 Then
                        Data(i, 2) = Application.Substitute(Data(i, 2), " ", " " & .Parent.Range("I" & i) & " ", .Parent.Range("J" & i).Value)
                    Else
                        Data(i, 2) = .Parent.Range("I" & i) & " " & Data(i, 2)
                    End If
                Next i
                .Value = Data
            End With
            ws.Range("D1") = "X"
        End If
    Next ws
    End Sub
    Last edited by Sintek; 04-28-2022 at 12:05 PM.

  9. #9
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    229

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    thankws very much

  10. #10
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    O365 V2408
    Posts
    15,138

    Re: add items into specific location for adjacent cells based on helper columns for two sh

    Glad to have helped...Tx for rep +

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] delete items from column for two sheets based on helper column
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2022, 01:14 PM
  2. [SOLVED] repeat missed items based on match helper column
    By abdo meghari in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-08-2022, 10:07 AM
  3. [SOLVED] repeating items and autofill based on match column with the others for specific sheets
    By leap out in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-25-2022, 02:03 PM
  4. VBA or Script to autohide/show columns based on helper cell - See Photo
    By kcstier in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 12-12-2019, 11:04 AM
  5. [SOLVED] Combine 2 Columns from 2 different sheets with helper
    By Sekars in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2017, 09:33 AM
  6. [SOLVED] Extract Unique Items (No VBA/No Helper Cells)
    By NeedForExcel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2015, 03:09 AM
  7. [SOLVED] Alternative to helper columns using VBA - Replace existing cells
    By helmerr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2014, 07:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1