read column row by row and reference to different sheet

[petebmor]

I am trying to write a code that looks down a column on one sheet and uses the cell value to reference a different sheet. Then take the adjacent cell value on the new sheet and place in power point. here is some of the code I have worked on. I have also attatched the workbook that it will be running in. Andrew coding practicemacro.xlsm

   ' declare the variable for the text question moving
        Dim myLines As Integer
        Dim myQues As String
        Dim myLoop As Integer
        Dim myQuestion As String
     
        ' finding the question info this will get the question number from responses sheet then look for that number on
        ' Questions-all then put that info into the powerpoint
        
        
        
        Sheets("responses").Select
        myLines = Range("A65535").End(xlUp).Row
        Sheets("Questions-all").Select
        For myLoop = 1 To myLines
        myQues = Sheets("responses").Range("A" & myLoop).Value
        Cells.Find(What:=myQues, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
        myQues = ActiveCell.responses
        Do Until myFirstNameAddress = myCurrAdd
        
        Cells.Find(What:=myQues, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
        myQues = ActiveCell.Address
        Loop
        myQues = ""
        Next
                        
        ' place the question data into the slide
            activeSlide.Shapes(2).TextFrame.TextRange.Text = Qest
        
        'Now let's change the font size of the callouts box
            activeSlide.Shapes(2).TextFrame.TextRange.Font.Size = 16
            
        ' reference back to the active sheet of the excel sheet.
        

        Next
     
    AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing
     Application.ScreenUpdating = True
    End Sub

[Tinbendr]

Then take the adjacent cell value on the new sheet

What are you talking about?

Can you give a real example?

[petebmor]

an example would be using the code com1 from the responses sheet and then finding it on the questions sheet. Once found taking the cell from column g of the same row and placing it in powerpoint. is that more clear?

[Tinbendr]

Sub TEST()

Dim RespLR As Long
Dim QuesLR As Long
Dim myQues As String
Dim myLoop As Integer
Dim C As Range
Dim FirstAddress As String

RespLR = Sheets("responses").Range("A65535").End(xlUp).Row
QuesLR = Sheets("Questions-All").Range("C65535").End(xlUp).Row

For myLoop = 1 To RespLR
myQues = Sheets("responses").Range("A" & myLoop).Value
If myQues <> "" Then
    With Sheets("Questions-all").Range("C2:C" & QuesLR)
        Set C = .Find(myQues, LookIn:=xlValues)
        If Not C Is Nothing Then
            FirstAddress = C.Address
            Do
                Debug.Print myQues & " - " & C.Address & C.Offset(, 4).Value
                'Powerpoint stuff here.
                
                Set C = .FindNext(C)
            Loop While Not C Is Nothing And C.Address <> FirstAddress
        End If
    End With
End If
Next
End Sub

[petebmor]

what variable are you using as the question string?

[petebmor]

everything is perfect but i cant get it to grab the question information. I get a output of either the cell location or the cell value like def2 com3. how do i take that piece of information and get it to grab the whole question. it is in sheet question-all column G.

[Tinbendr]

When the search looks for a match on the Questions-All sheet, C will reference the found text. (COM1)

From there, you use offset to reference the column over to the question.

C.Offset(, 4).Value

[petebmor]

i just get the same output when it runs. looks like it runs through all the questions and doesnt pick just one and then step through the next ones. it always puts out the same answer.

could this becaused by the fact that i am pulling graphs at the same time?

Dim RespLR As Long
        Dim QuesLR As Long
        Dim myQues As String
        Dim myLoop As Integer
        Dim C As Range
        Dim FirstAddress As String
        Dim Qest As String
    RespLR = Sheets("responses").Range("A500").End(xlUp).Row
    QuesLR = Sheets("Questions-All").Range("C500").End(xlUp).Row

    For myLoop = 1 To RespLR
    myQues = Sheets("responses").Range("A" & myLoop).Value
    If myQues <> "" Then
        With Sheets("Questions-all").Range("C2:C400" & QuesLR)
            Set C = .Find(myQues, LookIn:=xlValues)
            If Not C Is Nothing Then
                FirstAddress = C.Address
                Qest = C.Offset(, 4).Value
                Do
                 Debug.Print myQues & " - " & C.Address & C.Offset(, 4).Value
                    ' place the question data into the slide
                    activeSlide.Shapes(2).TextFrame.TextRange.Text = Qest
                    'Now let's change the font size of the callouts box
                    activeSlide.Shapes(2).TextFrame.TextRange.Font.Size = 16
                     Set C = .FindNext(C)
                Loop While Not C Is Nothing And C.Address <> FirstAddress
            End If
        End With
    End If
    Next
   
    
    
    
       
            
        ' reference back to the active sheet of the excel sheet.
        ThisWorkbook.Worksheets("bar graphs").Activate
        

        Next

[Tinbendr]

I'm still not quite sure what you're looking for... all the questions associated with the responses? But I'm still not clear how that correlates to the graphs.

But try this

Qest = Qest & C.Offset(, 4).Value & vbcr

[petebmor]

Thank you for your patience.

the macro is used to calculate survey data. the question is asked and the responses are given (1-5)

on the first sheet the responses are imputed.

the macro then takes the data creates graphs and moves those graphs and the original question to a powerpoint for presentation purposes. so the question and the graph is on the same slide of the powerpoint.

so the first sheet has the info of what question was asked and the responses each "person" gave.

Qest = Qest & C.Offset(, 4).Value & vbcr is close now it takes all the quesitons in sheet1 column 1

[Tinbendr]

Sorry for being dense. So each Bar chart is the responses to one question? And we need to find the corresponding question and insert that on the slide?

In the sample you u/l, there are responses to Question 1-9, 11 & 13. So then, do we need to pull only these questions?

Can you also u/l a finished Powerpoint result of what you expect? Maybe Q1-5.

I think you have two loops and you should only have one. Either the responses or the charts, but not both.

[petebmor]

all of the questions will have a question number eventually. so question 1-33 will have a question number. but the number of questions will change.

i think you are right if i have one loop this will be easier. ill post back what i come up with