Posts by Reiniervandijk1993

I have made some changes in the file. Hopefully it's now more clear

[xpost]

[/xpost]

Hi there,

I'm not good in programming and I hoped that people could help me on this platform.

I have made a macro that makes a productlist.

However some options of this productlist sometime need to be changed.

So I want a macro that scans my productlist and makes dropdownlists based on the list.

What this macro needs to do is:

1. It has to search for a word from the (productlist) subjectlist in another sheet.

   
   

   1. To do this correctly a cell before the subjectcell contains always: frm .

      
      

      1. Something I can"t find is the lookup function that is as follows: frm >cell< - (cell to the right)- Subject >Cell<
2. When cell is found then select till end > without the last row! named: Next
3. Then it needs to make a defined name for the selection.

   
   

   1. Code

      
      

      1. Application.CutCopyMode = False
      2. Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
4. When the name is made there has to come a dropdownlist made specially for the subject cell
5. And then is has to loop till the end of the productlist.

The code is missing the loop and automatically search option and make dropdownlist.

Sub test()
'
' test Macro
        Range("B4").Select
    
        Selection.Copy
    
        Range("C4").Select
    
        Cells.Find(What:="subject 1", After:=ActiveCell, LookIn:=xlFormulas2, _
                          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False).Activate
    
        Sheets("Sheet1").Select
    
   Cells.FindNext(After:=ActiveCell).Activate
       Range(Selection, Selection.End(xlDown)).Select
 
    
    Application.CutCopyMode = False
        Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
            False


       Sheets("Sheet2").Select
       Range("C4").Select
    
       With Selection.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                     xlBetween, Formula1:="=subject_1"
             .IgnoreBlank = False
             .InCellDropdown = True
             .InputTitle = ""
             .ErrorTitle = ""
             .InputMessage = ""
             .ErrorMessage = ""
             .ShowInput = True
             .ShowError = True
       End With
    
End Sub

The Cross-posting links are here;

http://www.vbaexpress.com/foru…cells&p=401675#post401675

https://www.mrexcel.com/board/…lls.1132666/#post-5478019

Hi

I'm building up a product list that every time is different.

What I want is building up text in a sentence with reference to another cell.

That means; text [A1] text.
The outcome must be, for example; text information text.

When the cell is visible, show the entire result.
But when selected to edit cell should show; text [A1] text visible.

This is what I have now

Sub Macro1()
Dim wdApp As Object
Dim oDoc As Object
Dim oRng As Object
Dim xlSheet As Worksheet
Dim LastRow As Long, i As Long
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    wdApp.Visible = True
    Set oDoc = wdApp.Documents.Add
    Set oRng = oDoc.Range
    oRng.collapse 0

    Set xlSheet = ActiveSheet
    With xlSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRow    'If there's no header row start from 1
            oRng.Text = .Cells(i, 1)
            If .Cells(i, 1).Font.Bold = True Then
                oRng.Style = "Heading 1"
            Else
                oRng.Style = "Heading 2"
            End If
            If i < LastRow Then oRng.InsertAfter vbCr
            Set oRng = oDoc.Range
            oRng.collapse 0
        Next i
    End With
    Set oRng = Nothing
    Set oDoc = Nothing
    Set wdApp = Nothing
    Set xlSheet = Nothing
End Sub

Thank you for your respons.

Your '' new user rules'' are quite clear to me now, Sorry for the mistake.

We need this in Word. I know how it works in Excel however this is not working for us.

Hi there,

My excel VBA experience is not good however I want to try something but can't work it out on my own.

I have a product list that will be different every time.

The list will take with it different cells to fill in different columns as well.

So this is the thing, I want a macro that opens Word and does the following;

Column A; check if the text is font bold or Normal.

Excel file;Font bold cell will be paste as Wordfile; Heading 1

Wordfile; Newline

Excel file;Normal cell - will be paste as Wordfile; Heading 2

etc. etc. till the end of the list in column A

Excel

Word

Then there is subject in x columns (subject from normal text to bold in the word file)

And then there is text in the cell crossing Name and Subject. (Cell text normal to normal in the Word file.

If the specific cell is empty then the Subject should not be transferred to Word.

See the pictures in this post for more information.

Excel Word

Second is actually when there is this >[cell] = ([1,1]) = ([r,c]) in a text cell.

Then print this in the Word document as the actual referredtext.
Example

Excelfile; text text text [1,1] text text
Wordfile; text text text example text text

Helping me with this will be appreciated. Thank you